﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices
Imports DatabaseAccess.DBUtility

Public Class uMESDispatchBusiness
    Implements uMESDispatchIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

#Region "修改报工零件编号信息"
    Function UpdateReportProductNoList(ByVal strReportID As String, ByVal dtChildContainer As DataTable) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateReportProductNoList
        Dim r As uMESProcessResult = dal.UpdateReportProductNoList(strReportID, dtChildContainer)

        Return r
    End Function
#End Region

#Region "获取产品合格证列表"
    Function GetQualificationList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetQualificationList
        Dim strQuery As String = "SELECT * FROM qualification q" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        If para.Keys.Contains("FactoryEName") = True Then
            strQuery += "AND q.mfgfactory = '" & para("FactoryEName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("FullName") = True Then
            strQuery += "AND q.checkemployee = '" & para("FullName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND LOWER(q.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("MfgTaskNo") = True Then
            strQuery += "AND LOWER(q.inventtaskname) LIKE '%" & para("MfgTaskNo").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(q.containername) LIKE '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "查询批次信息主SQL"
    Function GetContainerInfoSQL() As String
        'zll 20151029 添加：wfs.sequence获得当前工序的序号
        Dim strQuery As String = "SELECT wfs.sequence,c.containername,pb.productname,p.description,p.productrevision,p.productid,c.originalqty,c.qty,c.containercomments," & vbCrLf & _
                                "   wfb.workflowname,wf.workflowrevision,wf.workflowid,u.uomname,wc.workcentername,c.isselfmfg," & vbCrLf & _
                                "   sb.specname,s.specrevision,c.plannedstartdate,c.plannedcompletiondate,f.factoryname,c.materialrequirementdate," & vbCrLf & _
                                "   mo.mfgordername,o.ownername,pc.prioritycodename,wfs.workflowstepname,c.containerid,pf.productfamilyname," & vbCrLf & _
                                "   mo.mfgbatch,pm.phasemarkname,iti.inventtaskinfoname,c.containertype,c.togetherflag,pt.producttypename," & vbCrLf & _
                                "   (CASE WHEN" & vbCrLf & _
                                "       (SELECT hml.containerid" & vbCrLf & _
                                "       FROM historymainline hml" & vbCrLf & _
                                "       LEFT JOIN cdodefinition cd ON cd.cdodefid = hml.txntype" & vbCrLf & _
                                "       LEFT JOIN speclastcheckhistory slc ON slc.historymainlineid = hml.historymainlineid" & vbCrLf & _
                                "       WHERE cd.cdoname = 'SpecLastCheck' AND slc.checktype = 2 AND hml.reversalstatus = 1" & vbCrLf & _
                                "       AND ROWNUM = 1 AND (hml.containerid = c.containerid OR hml.containerid = c.parentcontainerid)) IS NULL THEN '0' ELSE '1' END" & vbCrLf & _
                                "   ) AS iszhongjian," & vbCrLf & _
                                "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN currentstatus cs ON cs.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cs.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cs.factoryid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cs.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                                "LEFT JOIN uom u ON u.uomid = c.uomid" & vbCrLf & _
                                "LEFT JOIN workcenter wc ON wc.workcenterid = c.workcenterid" & vbCrLf & _
                                "LEFT JOIN owner o ON o.ownerid = c.ownerid" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN inventtaskinfo iti ON iti.inventtaskinfoid = mo.inventtaskinfoid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        Return strQuery
    End Function
#End Region

#Region "获取与给定批次号的TogetherFlag相同的批次列表"
    Function GetContainerForTogetherFlag(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetContainerForTogetherFlag
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
        End If

        Dim strQuery As String = GetContainerInfoSQL()

        If strTogetherFlag <> "" Then
            strQuery += "AND c.togetherflag = '" & strTogetherFlag & "'" & vbCrLf
        Else
            strQuery += "AND c.containername = '" & strContainerName & "'" & vbCrLf
        End If

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

    Public Function GetContainer(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetContainer
        Dim strSql As String = String.Format("SELECT  t.* FROM CONTAINER t WHERE t.containername  like '{0}%'", strContainerName)
        Dim conTable As DataTable = dal.GetSpecDispatchInfo(strSql)

        Dim result As New uMESProcessResult
        result.Result = True
        result.DBTable = conTable
        Return result
    End Function

    Public Function GetContainer() As DataTable Implements uMESDispatchIBusiness.GetContainer
        Dim strSql As String = "SELECT t.qty, t.containerid, t.containername FROM CONTAINER t WHERE t.containername  LIKE 'A%S10.12.9201%'"
        Dim specTable As DataTable = dal.GetSpecDispatchInfo(strSql)
        Return specTable
    End Function
#Region "获取工段派工跟踪卡列表"
    Public Function GetWorkCenterDispatchContainerList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkCenterDispatchContainerList
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        Dim strContainerName As String = ""
        Dim strProductName As String = ""
        Dim strSpecID As String = ""
        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

        If para.Keys.Contains("ProductName") = True Then
            strProductName = para("ProductName").ToString.ToLower()
        End If

        If strProductName.Trim() = "" Then
            If para.Keys.Contains("ContainerName") = True Then
                strContainerName = commBusiness.ReplaceContainerName(para("ContainerName").ToString.Trim())
                If strContainerName.Trim() <> "" Then
                    Dim r1 As uMESProcessResult = GetContainerInfo(strContainerName)
                    If r1.Result = True Then
                        Try
                            strTogetherFlag = r1.DBTable.Rows(0)("TogetherFlag").ToString().Trim()
                            strProductName = r1.DBTable.Rows(0)("productname").ToString().Trim()
                            strSpecID = r1.DBTable.Rows(0)("specid").ToString().Trim()
                        Catch ex As Exception
                            strTogetherFlag = ""
                        End Try
                    End If
                End If
            End If
        End If


        'Dim strInfo As String = "开始查询" + DateTime.Now.ToString()

        Dim strQuery As String = "select * from conDispatch t "
        strQuery = strQuery + " WHERE 1=1 "

        If para.Keys.Contains("status") = True Then
            If para("status") = "待派工" Then
                strQuery += " AND t.attributevalue='1' "
            ElseIf para("status") = "待报工" Then
                strQuery += " AND t.attributevalue='2' "
            ElseIf para("status") = "待检验" Then
                strQuery += " AND t.attributevalue='3' "
            ElseIf para("status") = "已转工" Then
                strQuery += " AND t.attributevalue='4' "
            End If
        End If


        If para.Keys.Contains("FactoryName") = True Then
            strQuery += " AND t.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND t.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If

        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                If para("ContainerName").ToString().Trim() <> "" Then
                    strQuery += " AND LOWER(t.containername) = '" & strContainerName.ToLower() & "'" & vbCrLf
                End If
            Else
                strQuery += " AND LOWER(t.togetherflag) = '" & strTogetherFlag.ToLower & "' " & vbCrLf
                strQuery += " AND LOWER(t.productname) = '" & strProductName.ToLower() & "' " & vbCrLf
                strQuery += " AND (t.specid='" & strSpecID & "' OR t.revofrcdid='" & strSpecID & "')" & vbCrLf
            End If
        End If

        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(t.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%' " & vbCrLf
        End If
        If para.Keys.Contains("specName") = True Then
            strQuery += " AND LOWER(t.workflowstepname) LIKE '" & para("specName").ToString.ToLower & "%'" & vbCrLf
        End If

        If para.Keys.Contains("specno") = True Then
            strQuery += " AND LOWER(t.workflowstepname) LIKE '%" & para("specno").ToString.ToLower & "-%'" & vbCrLf
        End If

        If para.Keys.Contains("ProductName") = True Then
            strQuery += " AND LOWER(t.productname) LIKE '" & para("ProductName").ToString.ToLower & "%' " & vbCrLf
        End If


        strQuery += " ORDER BY t.containername "

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        'Try
        Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

        'strInfo = strInfo + "  结束查询" + DateTime.Now.ToString()

        Dim strSpecIDList As String = ""
        Dim strContainerIDList As String = ""

        Dim DT As DataTable = r.DBTable.Copy
        DT.Columns.Add("IsKey")  '关键工序
        DT.Columns.Add("SpecName")  '工序
        DT.Columns.Add("SpecRevision")  '工序版本
        DT.Columns.Add("status") '状态 
        DT.Columns.Add("specNameNew")
        DT.Columns.Add("fullname")
        DT.Columns.Add("dispStep")

        If DT.Rows.Count = 0 Then
            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount
            Return result
        End If

        Dim specIDList As IList(Of String) = New List(Of String)

        For Each row As DataRow In DT.Rows
            If row("specid").ToString().Trim() = "0000000000000000" Then
                row("specid") = row("revofrcdid")
            End If

            If row.IsNull("attributevalue") = False Then
                If Convert.ToInt32(row("attributevalue")) = 1 Then
                    row("status") = "待派工"
                ElseIf Convert.ToInt32(row("attributevalue")) = 2 Then
                    row("status") = "待报工"
                ElseIf Convert.ToInt32(row("attributevalue")) = 3 Then
                    row("status") = "待检验"
                ElseIf Convert.ToInt32(row("attributevalue")) = 4 Then
                    row("status") = "已转工"
                End If
            Else
                row("status") = " "
            End If


            Dim isHave As Integer = 0

            For i As Integer = 0 To specIDList.Count - 1
                If specIDList(i).Trim() = row("specid").ToString().Trim() Then
                    isHave = 1
                    Exit For
                End If
            Next

            If isHave = 0 Then
                strSpecIDList = strSpecIDList + "'" + row("specid").ToString().Trim() + "'" + ","
                specIDList.Add(row("specid").ToString().Trim())
            End If
            strContainerIDList = strContainerIDList + "'" + row("containerid").ToString().Trim() + "'" + ","
        Next

        strSpecIDList = strSpecIDList.Substring(0, strSpecIDList.Length - 1)
        strContainerIDList = strContainerIDList.Substring(0, strContainerIDList.Length - 1)

        ' strInfo = strInfo + " 结束整理" + DateTime.Now.ToString()

        '查找派工信息
        strQuery = "SELECT t.containerid,t.specid,nvl(t.qty,0) as qty ,nvl( t.reportedqty,0) as reportedqty,t.mainmfgemployeeid,e.fullname  FROM WORKCENTERDISPATCHING t " & vbCrLf
        strQuery += " LEFT JOIN employee e ON e.employeeid=t.mainmfgemployeeid " & vbCrLf
        strQuery += " WHERE  t.containerid IN (" + strContainerIDList + ")  AND t.specid IN (" + strSpecIDList + ")"
        Dim dispatchTable As DataTable = dal.GetSpecDispatchInfo(strQuery)

        Dim strFilter As String = ""

        For Each row As DataRow In DT.Rows

            strFilter = String.Format("specID='{0}' and containerid='{1}'", row("specid").ToString().Trim(), row("containerid").ToString().Trim())
            Dim dispatchRows As DataRow() = dispatchTable.Select(strFilter)

            If dispatchRows.Length > 0 Then
                row("fullname") = dispatchRows(0)("fullname").ToString()
            Else
                row("fullname") = ""
            End If
        Next

        '得到工序信息
        strQuery = "select sb.specname,s.specid,s.specrevision,s.iskey from spec s " & vbCrLf
        strQuery += "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf
        strQuery += "where s.specid in (" + strSpecIDList + ")"

        Dim specTable As DataTable = dal.GetSpecDispatchInfo(strQuery)

        For Each row As DataRow In DT.Rows
            strFilter = String.Format("specID='{0}'", row("specid").ToString().Trim())
            Dim specRows As DataRow() = specTable.Select(strFilter)
            row("specname") = specRows(0)("specname")
            row("SpecRevision") = specRows(0)("specrevision")
            If specRows.Length > 0 Then
                If specRows(0).IsNull("iskey") = True Then
                    row("IsKey") = "否"
                ElseIf specRows(0)("iskey").ToString().Trim() = "1" Then
                    row("IsKey") = "是"
                Else
                    row("IsKey") = "否"
                End If
            End If
            row("specNameNew") = CurrentInfo.ReplaceProductNameForSpec(row("specname").ToString().Trim())
            row("dispStep") = CurrentInfo.ReplaceProductNameForSpec(row("workflowstepname").ToString().Trim())
        Next

        ' strInfo = strInfo + " 得到工序信息完毕" + DateTime.Now
        result.Result = True
        result.DBTable = DT
        result.RowCount = r.RowCount

        Return result
    End Function
#End Region

#Region "获取需要外协的批次列表"
    ''' <summary>
    ''' zll 20151103 添加
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GeSynergicSpecValiateContainerList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GeSynergicSpecValiateContainerList
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        If para.Keys.Contains("ContainerName") = True Then
            Dim strContainerName As String = para("ContainerName").ToString
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If
        'zll 20151103 修改：增加外协信息
        Dim strQuery As String = "SELECT mt.*," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT fullname" & vbCrLf & _
                                "       FROM" & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT e.employeeid,e.employeename,e.fullname,wcd.containerid,wcd.specid" & vbCrLf & _
                                "           FROM workcenterdispatching wcd" & vbCrLf & _
                                "           LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "           WHERE wcd.dispatchtotype = 1" & vbCrLf & _
                                "           ORDER BY wcd.dispatchdate ASC" & vbCrLf & _
                                "       ) wcd2" & vbCrLf & _
                                "       WHERE ROWNUM = 1 AND wcd2.containerid = mt.containerid AND wcd2.specid = mt.nextSpecid" & vbCrLf & _
                                "   ) AS nextMainMfgEmployee" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "  SELECT Employee.Fullname,ssv.submitdate,ssv.auditingdate,AuditingEmployee.Fullname AS AuditingName," & vbCrLf & _
                                " mo.mfgordername, c.containername, pb.productname, p.description, p.productrevision, c.originalqty, c.qty, pc.prioritycodename, sb.specname, " & vbCrLf & _
                                "   s.specrevision,c.plannedstartdate,c.plannedcompletiondate,wfs.workflowid,c.containerid,pf.productfamilyname,pm.phasemarkname,wc.workcentername," & vbCrLf & _
                                "   mo.notes AS TechniqueNotes," & vbCrLf & _
                                "   (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "   (CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT wfs2.specid FROM" & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT wfs1.workflowid,wfs1.sequence," & vbCrLf & _
                                "               (CASE WHEN wfs1.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wfs1.specbaseid) ELSE wfs1.specid END) AS specid" & vbCrLf & _
                                "           FROM workflowstep wfs1 ORDER BY wfs1.sequence ASC" & vbCrLf & _
                                "       ) wfs2" & vbCrLf & _
                                "       WHERE ROWNUM = 1 AND wfs2.sequence > wfs.sequence AND wfs2.workflowid = wfs.workflowid" & vbCrLf & _
                                "   ) AS nextSpecid" & vbCrLf & _
                                "   FROM container c" & vbCrLf & _
                                "   LEFT JOIN synergicspecvalidate ssv ON ssv.Containerid=c.Containerid and nvl(ssv.status,1) = 1 " & vbCrLf & _
                                "   LEFT JOIN Employee ON Employee.Employeeid = ssv.Submitemployeeid" & vbCrLf & _
                                "   LEFT JOIN Employee AuditingEmployee ON AuditingEmployee.Employeeid = ssv.Auditingemployeeid" & vbCrLf & _
                                "   LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "   LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid" & vbCrLf & _
                                "   LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "   LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "   LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "   LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "   LEFT JOIN PriorityCode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "   LEFT JOIN factory f on f.factoryid = cu.factoryid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN workflow w ON w.workflowid = wfs.workflowid" & vbCrLf & _
                                "   LEFT JOIN cdodefinition cdo ON cdo.cdodefid = w.cdotypeid" & vbCrLf & _
                                "   LEFT JOIN workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                                "   WHERE 1 = 1 AND wb.workflowname<>'MW'" & vbCrLf & _
                                "   AND c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "   AND c.containerid NOT IN" & vbCrLf & _
                                "      (" & vbCrLf & _
                                "          SELECT hml.containerid" & vbCrLf & _
                                "          FROM historymainline hml" & vbCrLf & _
                                "          LEFT JOIN cdodefinition cd ON cd.cdodefid = hml.txntype" & vbCrLf & _
                                "          LEFT JOIN speclastcheckhistory slc ON slc.historymainlineid = hml.historymainlineid" & vbCrLf & _
                                "          WHERE cd.cdoname = 'SpecLastCheck' AND slc.checktype = 2 AND hml.reversalstatus = 1" & vbCrLf & _
                                "      )" & vbCrLf

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND wc.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
            Else
                strQuery += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            End If
        End If
        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("PriorityCode") = True Then
            strQuery += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND c.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND c.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        strQuery += ") mt" & vbCrLf & _
                    "ORDER BY mt.containername ASC" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            Dim DT As DataTable = r.DBTable.Copy
            DT.Columns.Add("NextMainMfg")
            'For i As Integer = 0 To DT.Rows.Count - 1
            '    If Not IsDBNull(DT.Rows(i)("nextMainMfgEmployee")) Then
            '        Dim strHypeLink As String = ""
            '        strHypeLink = "<a href = '#' onclick = ""ViewDispatchAndReport('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("NextSpecID").ToString & "')"" style='font-size:12px; color:blue;'>" & DT.Rows(i)("nextMainMfgEmployee").ToString & "</a>"
            '        DT.Rows(i)("NextMainMfg") = strHypeLink
            '    Else
            '        DT.Rows(i)("NextMainMfg") = ""
            '    End If
            'Next

            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

#End Region

#Region "获取生产问题列表"
    Function GetMfgProblemList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetMfgProblemList
        Dim strQuery As String = "SELECT up.ufcproblemname,ce.employeename,ce.fullname,up.ufccreatedate,f.factoryname,upt.ufcproblemtypename" & vbCrLf & _
                                "   ,r.resourcename,toe.employeename AS toemployeename,toe.fullname AS tofullname,up.ufcwtdj" & vbCrLf & _
                                "   ,pb.productname,p.productrevision,p.description,c.containername,c.qty,up.ufcljxh,up.ufccontent,up.ufcResult" & vbCrLf & _
                                "   ,CASE WHEN up.ufcstatus=0 THEN '已关闭' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '已处理' END AS ufcstatus" & vbCrLf & _
                                "   ,CASE WHEN LOWER(ce.employeename)='' AND up.ufcresult IS NULL THEN 1 ELSE 0 END AS tjrflag" & vbCrLf & _
                                "FROM ufcproblem up" & vbCrLf & _
                                "LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = up.resourceid" & vbCrLf & _
                                "LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = up.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = up.containerid" & vbCrLf & _
                                "LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = up.factoryid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND (f.factoryname = '" & para("FactoryName").ToString & "' OR up.factoryid IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("CreateEmployee") = True Then
            strQuery += "AND LOWER(ce.fullname) LIKE '%" & para("CreateEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ToEmployee") = True Then
            strQuery += "AND LOWER(toe.fullname) LIKE '%" & para("ToEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND up.ufccreatedate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND up.ufccreatedate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工序派工信息"
    ''' <summary>
    ''' zll 20151027 修改：隐藏已派工的工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSpecDispatchInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetSpecDispatchInfo
        Dim strWorkflowID As String = para("WorkflowID").ToString
        Dim strContainerID As String = para("ContainerID").ToString
        Dim strQty As String = para("Qty").ToString

        'Dim strQuery As String = "SELECT ta.specname,ta.specrevision,ta.specid,(CASE WHEN ta.dispatched IS NULL THEN 0 ELSE ta.dispatched END) AS dispatched," & vbCrLf & _
        '                        "   (CASE WHEN (" & strQty & " - (CASE WHEN ta.dispatched IS NULL THEN 0 ELSE ta.dispatched END)) > 0 THEN (" & strQty & " - (CASE WHEN ta.dispatched IS NULL THEN 0 ELSE ta.dispatched END)) ELSE 0 END) AS willdispatch," & vbCrLf & _
        '                        "   '" & strContainerID & "' AS containerid," & vbCrLf & _
        '                        "   (CASE WHEN ta.reportedqty IS NULL THEN 0 ELSE ta.reportedqty END) AS reportedqty,ta.sequence," & vbCrLf & _
        '                        "   (CASE WHEN ta.iskey = 1 THEN 'G' ELSE '' END) AS iskey" & vbCrLf & _
        '                        "FROM" & vbCrLf & _
        '                        "(" & vbCrLf & _
        '                        "   SELECT sb.specname,s.specrevision,s.specid,wfss.sequence,s.iskey," & vbCrLf & _
        '                        "       (SELECT SUM(qty) AS qty FROM workcenterdispatching wcd WHERE wcd.specid=s.specid AND wcd.dispatchtype = 0 AND wcd.containerid = '" & strContainerID & "') AS dispatched," & vbCrLf & _
        '                        "       (SELECT SUM(reportedqty) AS reportedqty FROM workcenterdispatching wcd WHERE wcd.specid=s.specid AND wcd.dispatchtotype = 1 AND wcd.containerid = '" & strContainerID & "') AS reportedqty" & vbCrLf & _
        '                        "   FROM spec s" & vbCrLf & _
        '                        "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
        '                        "   RIGHT JOIN" & vbCrLf & _
        '                        "   (" & vbCrLf & _
        '                        "       SELECT wfs.sequence," & vbCrLf & _
        '                        "           (CASE WHEN wfs.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = wfs.specbaseid) ELSE specid END) AS specid" & vbCrLf & _
        '                        "       FROM workflowstep wfs" & vbCrLf & _
        '                        "       WHERE wfs.workflowid = '" & strWorkflowID & "'" & vbCrLf & _
        '                        "       ORDER BY wfs.sequence" & vbCrLf & _
        '                        "   ) wfss ON s.specid = wfss.specid" & vbCrLf & _
        '                        "   WHERE s.iscooperation = 2 OR s.iscooperation IS NULL" & vbCrLf

        ' ''排除检验工序
        'Dim strCheckSpecKey As String = para("CheckSpecKey").ToString
        'If strCheckSpecKey <> "" Then
        '    Dim arrayS As String() = strCheckSpecKey.Split(",")
        '    If arrayS.Length > 0 Then
        '        For i As Integer = 0 To arrayS.Length - 1
        '            strQuery += "   AND sb.specname NOT LIKE '%" & arrayS(i).ToString & "%'" & vbCrLf
        '        Next
        '    End If
        'End If

        ' ''排除清洗工序
        'Dim strCleanSpecKey As String = para("CleanSpecKey").ToString
        'If strCleanSpecKey <> "" Then
        '    Dim arrayC As String() = strCleanSpecKey.Split(",")
        '    If arrayC.Length > 0 Then
        '        For i As Integer = 0 To arrayC.Length - 1
        '            strQuery += "   AND sb.specname NOT LIKE '%" & arrayC(i).ToString & "%'" & vbCrLf
        '        Next
        '    End If
        'End If

        'strQuery += "   AND s.specid NOT IN" & vbCrLf & _
        '            "       (" & vbCrLf & _
        '            "           SELECT i.specid FROM synergicspecinfo i" & vbCrLf & _
        '            "           LEFT JOIN synergicspecvalidate ssv ON ssv.synergicspecvalidateid = i.synergicspecvalidateid" & vbCrLf & _
        '            "           WHERE ssv.containerid = '" & strContainerID & "'" & vbCrLf & _
        '            "       )" & vbCrLf & _
        '            "   AND s.specid NOT IN" & vbCrLf & _
        '            "       (" & vbCrLf & _
        '            "           SELECT syi.specid FROM synergicinfo syi" & vbCrLf & _
        '            "           WHERE syi.containerid = '" & strContainerID & "'" & vbCrLf & _
        '            "       )" & vbCrLf & _
        '            ") ta" & vbCrLf & _
        '            "ORDER BY ta.sequence ASC" & vbCrLf

        Dim result As New uMESProcessResult


        Try

            Dim workFlowDT As DataTable = GetStepInfo(strWorkflowID)

            Dim strQuery As String = "select d.specid,d.containerid ,c.qty as conqty, sum(d.qty) as qty,sum(d.reportedqty) as reportedqty  from workcenterdispatching d " & vbCrLf & _
                                     "left join container c on c.containerid=d.containerid " & vbCrLf & _
                                     "where  d.containerid='" & strContainerID & "' and d.reportedqty <=c.qty " & vbCrLf & _
                                     "group by d.specid,d.containerid,c.qty  " & vbCrLf
            Dim dispatchDT As DataTable = dal.GetSpecDispatchInfo(strQuery)

            strQuery = "SELECT i.specid FROM synergicspecinfo i " & vbCrLf & _
                       "LEFT JOIN synergicspecvalidate ssv ON ssv.synergicspecvalidateid = i.synergicspecvalidateid " & vbCrLf & _
                       "WHERE ssv.containerid = '" & strContainerID & "' " & vbCrLf & _
                       "union SELECT syi.specid FROM synergicinfo syi   WHERE syi.containerid = '" & strContainerID & "'"
            Dim synerDT As DataTable = dal.GetSpecDispatchInfo(strQuery)

            Dim dispTable As DataTable = New DataTable()

            dispTable.Columns.Add("SpecName")    '工序名称
            dispTable.Columns.Add("SpecRevision")    '工序名称
            dispTable.Columns.Add("Dispatched")    '已派
            dispTable.Columns.Add("WillDispatch")  '待派
            dispTable.Columns.Add("ReportedQty")   '已报
            dispTable.Columns.Add("Seq")           '顺序号
            dispTable.Columns.Add("ViewDispatchAndReport")

            Dim intSeq As Integer = 0
            '整理子工艺路线
            Dim dispWorkFlowDT As DataTable = workFlowDT.Clone()
            For Each row As DataRow In workFlowDT.Rows
                If row("specid1").ToString().Trim() = "" Then
                    Dim subWorkFlowDT As DataTable = New DataTable()

                    If row("subworkflowid").ToString().Trim() <> "0000000000000000" Then
                        Dim subWorkFlowID As String = row("subworkflowid").ToString().Trim()
                        subWorkFlowDT = GetStepInfo(strWorkflowID)
                        For Each subRow As DataRow In subWorkFlowDT.Rows
                            intSeq = intSeq + 1
                            subRow("sequence") = intSeq
                            dispWorkFlowDT.ImportRow(subRow)
                        Next
                    Else
                        Dim subWorkFlowID As String = row("revofrcdid").ToString().Trim()
                        subWorkFlowDT = GetStepInfo(subWorkFlowID)
                        For Each subRow As DataRow In subWorkFlowDT.Rows
                            intSeq = intSeq + 1
                            subRow("sequence") = intSeq
                            dispWorkFlowDT.ImportRow(subRow)
                        Next
                    End If
                Else
                    intSeq = intSeq + 1
                    row("sequence") = intSeq
                    dispWorkFlowDT.ImportRow(row)
                End If
            Next

            Dim strCheckSpecKey As String = para("CheckSpecKey").ToString
            Dim strCleanSpecKey As String = para("CleanSpecKey").ToString

            For Each row As DataRow In dispWorkFlowDT.Rows
                If row("operationname").ToString = strCheckSpecKey Then
                    Continue For
                End If

                If row("specname").ToString().IndexOf(strCleanSpecKey) <> -1 Then
                    Continue For
                End If

                Dim strFilter As String = String.Format("specid='{0}'", row("specid1").ToString().Trim())
                Dim syncRows As DataRow() = synerDT.Select(strFilter)
                If syncRows.Length > 0 Then
                    Continue For
                End If

                Dim newRow As DataRow = dispTable.NewRow()
                newRow("SpecName") = row("specname")
                newRow("SpecRevision") = row("specrevision")
                strFilter = String.Format("specID='{0}'", row("specid1").ToString().Trim())
                Dim dispatchRows As DataRow() = dispatchDT.Select(strFilter)
                If dispatchRows.Length = 0 Then
                    newRow("Dispatched") = 0
                    newRow("ReportedQty") = 0
                    newRow("WillDispatch") = strQty
                Else
                    newRow("Dispatched") = dispatchRows(0)("qty")
                    newRow("ReportedQty") = dispatchRows(0)("reportedqty")
                    Dim willQty As Integer = Convert.ToInt32(dispatchRows(0)("conqty")) - Convert.ToInt32(dispatchRows(0)("qty"))
                    newRow("WillDispatch") = willQty
                End If
                dispTable.Rows.Add(newRow)
            Next

            'zll 20151027 添加 去除一派工工序
            For i As Integer = dispTable.Rows.Count - 1 To 0 Step -1
                If Convert.ToInt32(dispTable.Rows(i)("Dispatched")) >= Convert.ToInt32(para("Qty")) Then
                    dispTable.Rows.RemoveAt(i)
                End If
            Next
            result.Result = True
            result.DBTable = dispTable
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "批量派工获取工序列表信息"
    ''' <summary>
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMultiSpecDispatchInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetMultiSpecDispatchInfo
        Dim strWorkflowID As String = para("WorkflowID").ToString
        Dim strContainerID As String = para("ContainerID").ToString
        Dim strQty As String = para("Qty").ToString
        Dim result As New uMESProcessResult

        Try
            Dim workFlowDT As DataTable = GetStepInfo(strWorkflowID)

            Dim strQuery As String = "select d.specid,d.containerid ,c.qty as conqty, sum(d.qty) as qty,sum(d.reportedqty) as reportedqty  from workcenterdispatching d " & vbCrLf & _
                                     "left join container c on c.containerid=d.containerid " & vbCrLf & _
                                     "where  d.containerid='" & strContainerID & "' group by d.specid,d.containerid,c.qty  " & vbCrLf
            Dim dispatchDT As DataTable = dal.GetSpecDispatchInfo(strQuery)

            strQuery = "SELECT i.specid FROM synergicspecinfo i " & vbCrLf & _
                       "LEFT JOIN synergicspecvalidate ssv ON ssv.synergicspecvalidateid = i.synergicspecvalidateid " & vbCrLf & _
                       "WHERE ssv.containerid = '" & strContainerID & "' " & vbCrLf & _
                       "union SELECT syi.specid FROM synergicinfo syi   WHERE syi.containerid = '" & strContainerID & "'"
            Dim synerDT As DataTable = dal.GetSpecDispatchInfo(strQuery)

            Dim dispTable As DataTable = New DataTable()

            dispTable.Columns.Add("SpecName")    '工序名称
            dispTable.Columns.Add("SpecRevision")    '工序名称
            dispTable.Columns.Add("Dispatched")    '已派
            dispTable.Columns.Add("WillDispatch")  '待派
            dispTable.Columns.Add("ReportedQty")   '已报
            dispTable.Columns.Add("Seq")           '顺序号
            dispTable.Columns.Add("ViewDispatchAndReport")

            Dim intSeq As Integer = 0
            '整理子工艺路线
            Dim dispWorkFlowDT As DataTable = workFlowDT.Clone()
            For Each row As DataRow In workFlowDT.Rows
                If row("specid1").ToString().Trim() = "" Then
                    Dim subWorkFlowDT As DataTable = New DataTable()

                    If row("subworkflowid").ToString().Trim() <> "0000000000000000" Then
                        Dim subWorkFlowID As String = row("subworkflowid").ToString().Trim()
                        subWorkFlowDT = GetStepInfo(strWorkflowID)
                        For Each subRow As DataRow In subWorkFlowDT.Rows
                            intSeq = intSeq + 1
                            subRow("sequence") = intSeq
                            dispWorkFlowDT.ImportRow(subRow)
                        Next
                    Else
                        Dim subWorkFlowID As String = row("revofrcdid").ToString().Trim()
                        subWorkFlowDT = GetStepInfo(subWorkFlowID)
                        For Each subRow As DataRow In subWorkFlowDT.Rows
                            intSeq = intSeq + 1
                            subRow("sequence") = intSeq
                            dispWorkFlowDT.ImportRow(subRow)
                        Next
                    End If
                Else
                    intSeq = intSeq + 1
                    row("sequence") = intSeq
                    dispWorkFlowDT.ImportRow(row)
                End If
            Next

            Dim strCheckSpec1Key As String = para("CheckSpecKey").ToString.Trim()
            'Dim strCheckSpec2Key As String = para("CheckSpec2Key").ToString.Trim()

            'Dim strCleanSpecKey As String = para("CleanSpecKey").ToString

            For Each row As DataRow In dispWorkFlowDT.Rows
                If row("operationname").ToString() = strCheckSpec1Key Then
                    Continue For
                End If

                'If row("specname").ToString().IndexOf(strCleanSpecKey) <> -1 Then
                '    Continue For
                'End If

                Dim strFilter As String = String.Format("specid='{0}'", row("specid1").ToString().Trim())
                Dim syncRows As DataRow() = synerDT.Select(strFilter)
                If syncRows.Length > 0 Then
                    Continue For
                End If

                Dim newRow As DataRow = dispTable.NewRow()
                newRow("SpecName") = row("specname")
                newRow("SpecRevision") = row("specrevision")
                strFilter = String.Format("specID='{0}'", row("specid1").ToString().Trim())
                Dim dispatchRows As DataRow() = dispatchDT.Select(strFilter)
                If dispatchRows.Length = 0 Then
                    newRow("Dispatched") = 0
                    newRow("ReportedQty") = 0
                    newRow("WillDispatch") = strQty
                Else
                    newRow("Dispatched") = dispatchRows(0)("qty")
                    newRow("ReportedQty") = dispatchRows(0)("reportedqty")
                    Dim willQty As Integer = Convert.ToInt32(dispatchRows(0)("conqty")) - Convert.ToInt32(dispatchRows(0)("qty"))
                    newRow("WillDispatch") = willQty
                End If
                dispTable.Rows.Add(newRow)
            Next

            Dim tmpTable As DataTable = dispTable.Clone()

            For Each row As DataRow In dispTable.Rows
                If Convert.ToInt32(strQty) > Convert.ToInt32(row("Dispatched")) Then
                    tmpTable.ImportRow(row)
                End If
            Next
            result.Result = True
            result.DBTable = tmpTable
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "批量派工获取工序列表信息(单件单卡和数量批次)"
    
    Public Function GetMultiSpecDispatchAllInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetMultiSpecDispatchAllInfo
        Dim strWorkflowID As String = para("WorkflowID").ToString
        Dim strContainerID As String = para("ContainerID").ToString
        Dim strQty As String = para("Qty").ToString
        Dim strContainerIDList As String = para("ContainerList").ToString()
        'Dim strCheckSpec1Key As String = para("CheckSpec1Key").ToString.Trim()
        ' Dim strCheckSpec2Key As String = para("CheckSpec2Key").ToString.Trim()

        Dim result As New uMESProcessResult

        Try
            Dim workFlowDT As DataTable = GetStepInfo(strWorkflowID) '得到工艺路线
            'Dim dispWorkFlowDT As DataTable = workFlowDT.Clone()
            Dim dispWorkFlowDT1 As DataTable = workFlowDT.Clone()

            '去掉检验工序
            'For Each row As DataRow In workFlowDT.Rows
            '    If row("operationname").ToString() = strCheckSpec1Key Then
            '        Continue For
            '    End If
            '    dispWorkFlowDT.ImportRow(row)
            'Next

            '整理查询队列
            Dim strSpecList As String = ""
            For Each row As DataRow In workFlowDT.Rows
                strSpecList = strSpecList + "'" + row("specid1").ToString().Trim() + "',"
            Next
            strSpecList = strSpecList.Substring(0, strSpecList.Length - 1)

            '检查转工申请
            Dim strQuery As String = "SELECT t.synergicspecvalidatename,t.containerid,st1.specid FROM SynergicSpecValidate t " & vbCrLf & _
                                     "LEFT JOIN SynergicSpecValidateSynergicSp st ON st.synergicspecvalidateid=t.synergicspecvalidateid " & vbCrLf & _
                                     "LEFT JOIN SynergicSpecInfo st1 ON st1.synergicspecinfoid=st.synergicspecinfoid " & vbCrLf & _
                                     "WHERE t.containerid IN (" & strContainerIDList & ") AND st1.specid IN (" & strSpecList & ")"

            Dim syncDT As DataTable = dal.GetSpecDispatchInfo(strQuery)

            '去掉已经转工提请的工序
            For Each row As DataRow In workFlowDT.Rows
                Dim strSpecID As String = row("specid1").ToString().Trim()
                Dim tmpRows As DataRow() = syncDT.Select("specid='" & strSpecID & "'")
                If tmpRows.Length = 0 Then
                    dispWorkFlowDT1.ImportRow(row)
                End If
            Next

            strQuery = "select d.specid,d.containerid ,c.qty as conqty, d.qty as qty,d.reportedqty  from workcenterdispatching d " & vbCrLf & _
                       "left join container c on c.containerid=d.containerid " & vbCrLf & _
                       "where d.containerid in (" & strContainerIDList & ") and d.specid in (" & strSpecList & ")"

            Dim dispatchDT As DataTable = dal.GetSpecDispatchInfo(strQuery)

            Dim dispTable As DataTable = New DataTable()

            dispTable.Columns.Add("SpecName")    '工序名称
            dispTable.Columns.Add("SpecRevision")    '工序名称
            dispTable.Columns.Add("Dispatched")    '已派
            dispTable.Columns.Add("WillDispatch")  '待派
            dispTable.Columns.Add("ReportedQty")   '已报
            dispTable.Columns.Add("Seq")           '顺序号
            dispTable.Columns.Add("specID")    '工序名称
            dispTable.Columns.Add("ViewDispatchAndReport")

            Dim intSeq As Integer = 0

            Dim strContinerArray As String() = strContainerIDList.Split(",")
            Dim intQty As Integer = Convert.ToInt32(strQty)      '批次数量


            For Each row As DataRow In dispWorkFlowDT1.Rows
                Dim dispQty As Integer = 0 '已经派工数量
                Dim intWillQty As Integer = 0  '待派工数量

                For i As Integer = 0 To strContinerArray.Length - 1
                    Dim strFilter As String = String.Format("containerid={0} and specID='{1}'", strContinerArray(i).Trim(), row("specid1").ToString().Trim())
                    Dim tmpRows As DataRow() = dispatchDT.Select(strFilter)
                    If tmpRows.Length = 0 Then
                        Continue For
                    End If
                    For Each tmpRow As DataRow In tmpRows
                        dispQty = dispQty + Convert.ToInt32(tmpRow("qty"))
                    Next
                Next
                If intQty > dispQty Then
                    Dim newrow As DataRow = dispTable.NewRow()
                    newrow("SpecName") = row("specname")
                    newrow("SpecRevision") = row("specrevision")
                    newrow("Dispatched") = dispQty
                    newrow("WillDispatch") = intQty - dispQty
                    newrow("Seq") = row("sequence")
                    newrow("specID") = row("specid1").ToString().Trim()
                    dispTable.Rows.Add(newrow)
                End If
            Next
            result.Result = True
            result.DBTable = dispTable
            result.DBDataSet.Tables.Add(dispatchDT)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

#End Region

    Private Function GetStepInfo(ByVal strWorkflowID As String) As DataTable
        Dim strQuery As String = "select  sb.specname,s.specrevision,o.operationname,stepInfo.* from (select  " & vbCrLf & _
                                    "(CASE WHEN t.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = t.specbaseid) when t.specid is null then ' ' ELSE specid END) AS specid1, " & vbCrLf & _
                                    "t.workflowstepname,t.subworkflowbaseid,t.subworkflowid, t.sequence,wb.revofrcdid  from workflowstep t " & vbCrLf & _
                                    "left join workflowbase wb on wb.workflowbaseid=t.subworkflowbaseid " & vbCrLf & _
                                    "WHERE t.workflowid = '" & strWorkflowID & "')  stepInfo " & vbCrLf & _
                                    "left join spec s on s.specid=stepInfo.specid1 " & vbCrLf & _
                                    "left join specbase sb  on sb.specbaseid=s.specbaseid " & vbCrLf & _
                                    "left join operation o  on o.operationid=s.operationid " & vbCrLf & _
                                    "ORDER BY stepInfo.sequence ASC" & vbCrLf
        Dim workFlowDT As DataTable = dal.GetSpecDispatchInfo(strQuery)
        GetStepInfo = workFlowDT
    End Function

#Region "工时相关"
    'zll 20141027 修改：增加分厂、工区、班组、工种、工序及产品型号查询类别
    Function GetWorkingReportList(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkingReportList
        GetWorkingReportList = New uMESProcessResult
        Dim sb As New StringBuilder()

        sb.AppendLine("select f.factoryname,w.workcentername,t.teamname,o.operationname,pf.productfamilyname,c.containername,wrh.qty,hm.txndate,em.employeeid, em.employeename,em.fullname," &
            "wrh.workingreporthistoryid,wrh.SUBMMITDATE, wfs.workflowstepname, s.specid, nvl(std.durationperunit,spd.durationperunit*24*60) as durationperunit, c.productid, " &
            "p.productbaseid,pb.productname,(wrh.qty)* nvl(std.durationperunit,spd.durationperunit*24*60) as sumworktime ")
        sb.AppendLine("from workingreporthistory wrh")
        sb.AppendLine("inner join conventioncheckhistory cc on cc.workingreportid=wrh.workingreporthistoryid")
        sb.AppendLine("left join historymainline hm on hm.historymainlineid = wrh.historymainlineid ")
        sb.AppendLine("left join employee em on em.employeeid = hm.employeeid")
        sb.AppendLine("left join sessionvalues se on se.employeeid = em.employeeid")  '增加工种查询条件
        sb.AppendLine("left join teamemployee te on te.employeeid = em.employeeid")   '增加班组查询条件
        sb.AppendLine("left join workflowstep wfs on wfs.workflowstepid=hm.workflowstepid")
        sb.AppendLine("left join spec s on s.specid=wfs.specid")
        sb.AppendLine("left join specbase sb on sb.specbaseid=wfs.specbaseid")
        'sb.AppendLine(" left join spec s1 on s1.specbaseid=sb.specbaseid") '去除工时重复项

        sb.AppendLine("left join specschedulingdetail spd on spd.specid=nvl(s.specid,sb.revofrcdid)")
        sb.AppendLine("left join stepschedulingdetail std on std.stepschedulingdetailid=wfs.schedulingdetailid")
        sb.AppendLine("left join container c on c.containerid=hm.containerid ")
        sb.AppendLine("left join product p on p.productid=c.productid")
        sb.AppendLine("left join productbase pb on pb.productbaseid=p.productbaseid ")

        '班组
        'sb.AppendLine("left join workcenterdispatching wcd on wcd.workcenterdispatchingid=wrh.workcenterdispatchingid ") '班组查询条件是员工所属班组
        sb.AppendLine("left join team t on t.teamid=te.teamid ") '班组查询条件是员工所属班组
        '工种
        sb.AppendLine("left join operation o on o.operationid = se.operationid")
        '分厂、工区
        sb.AppendLine("left join sessionvalues sv on sv.employeeid=em.employeeid ")
        sb.AppendLine("left join factory f on f.factoryid=sv.factoryid ")
        sb.AppendLine("left join workcenter w on w.workcenterid=sv.workcenterid ")
        '产品类型
        sb.AppendLine("left join productfamily pf on pf.productfamilyid=p.productfamilyid ")


        If para("employeename").ToString() = "全部" Then
            sb.AppendLine(" where hm.reversalstatus =1 and c.status<>0 ")
        Else
            sb.AppendFormat(" where hm.reversalstatus =1 and lower(em.employeename)='{0}' and c.status<>0 ", para("employeename").ToString.ToLower)
        End If

        If para("factoryname").ToString() <> "全部" Then
            sb.AppendFormat(" and f.factoryname='{0}' ", para("factoryname").ToString)
        End If
        If para("workcentername").ToString() <> "全部" Then
            sb.AppendFormat(" and w.workcentername='{0}' ", para("workcentername").ToString)
        End If
        If para("teamname").ToString() <> "全部" Then
            sb.AppendFormat(" and t.teamname='{0}' ", para("teamname").ToString)
        End If
        If para("operationname").ToString() <> "全部" Then
            sb.AppendFormat(" and o.operationname='{0}' ", para("operationname").ToString)
        End If
        If para("productfamilyname").ToString() <> "全部" Then
            sb.AppendFormat(" and pf.productfamilyname='{0}' ", para("productfamilyname").ToString)
        End If

        If para.ContainsKey("specname") Then
            sb.AppendFormat(" and sb.specname='{0}' ", para("specname"))
        End If

        If para.ContainsKey("specno") Then
            'sb.AppendFormat(" and s.specno='{0}' ", para("specno"))
            sb.AppendLine(" and wfs.workflowstepname like '" & para("specno") & "-%'")
        End If

        If para.ContainsKey("productname") Then
            sb.AppendFormat(" and pb.productname='{0}' ", para("productname"))
        End If
        If para.ContainsKey("containername") Then
            sb.AppendFormat(" and c.containername='{0}' ", para("containername"))
        End If
        If para.ContainsKey("status") Then
            If para("status").ToString() = "0" Then
                sb.AppendFormat(" and wrh.SUBMMITDATE is null ")
            ElseIf para("status").ToString() = "1" Then
                sb.AppendFormat(" and wrh.SUBMMITDATE is not null ")
            End If
            sb.AppendFormat(" and hm.txndate between to_date('{0}','yyyy-mm-dd hh24:mi:ss') and to_date('{1}','yyyy-mm-dd hh24:mi:ss') ",
                para("begindate"), para("enddate"))
        Else
            sb.AppendFormat(" and wrh.SUBMMITDATE between to_date('{0}','yyyy-mm-dd hh24:mi:ss') and to_date('{1}','yyyy-mm-dd hh24:mi:ss') ",
                      para("begindate"), para("enddate"))
        End If
        sb.AppendLine(" order by hm.txndate desc")

        Dim str1Tmp As String = sb.ToString()
        Dim tmpTable As DataTable = dal.StandardGetDataTable(sb.ToString)

        Dim strTmp As String = para("workTimeDate").ToString().Trim()
        If strTmp.Trim() <> "" Then
            Dim dateTmp As DateTime = Convert.ToDateTime(strTmp)
            For Each row As DataRow In tmpTable.Rows
                If Convert.ToDateTime(row("txndate")) <= dateTmp Then
                    row("sumworktime") = 0
                End If
            Next
        End If

        Dim tmpSet As DataSet = New DataSet()
        tmpSet.Tables.Add(tmpTable)

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBDataSet = tmpSet.Copy()
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        strTmp = para("workTimeDate").ToString().Trim()
        If strTmp.Trim() <> "" Then
            Dim dateTmp As DateTime = Convert.ToDateTime(strTmp)
            For Each row As DataRow In result.DBTable.Rows
                If Convert.ToDateTime(row("txndate")) <= dateTmp Then
                    row("sumworktime") = 0
                End If
            Next
        End If
        GetWorkingReportList = result
    End Function

    Function SaveWorkingReportSubmmitDate(ByVal dtworking As DataTable) As uMESProcessResult Implements uMESDispatchIBusiness.SaveWorkingReportSubmmitDate
        SaveWorkingReportSubmmitDate = dal.SaveWorkingReportSubmmitDate(dtworking)
    End Function

    Function GetStepInfo(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult Implements uMESDispatchIBusiness.GetStepInfo
        GetStepInfo = New uMESProcessResult
        Dim sb As New StringBuilder()

        sb.AppendLine("select wfs.workflowstepname, wfs.notes,wfb.workflowname||':'||wf.workflowrevision workflow, " &
                      "nvl(std.durationperunit,spd.durationperunit*24*60) as durationperunit, wfs.workflowstepid")
        sb.AppendLine("from workflowstep wfs ")
        sb.AppendLine("left join spec s on s.specid=wfs.specid")
        sb.AppendLine("left join specbase sb on sb.specbaseid=wfs.specbaseid")
        sb.AppendLine("left join specschedulingdetail spd on spd.specid=nvl(s.specid,sb.revofrcdid)")
        sb.AppendLine("left join stepschedulingdetail std on std.stepschedulingdetailid=wfs.schedulingdetailid")
        sb.AppendLine("left join workflow wf on wf.workflowid = wfs.workflowid")
        sb.AppendLine("left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid")
        sb.AppendFormat(" where wfb.workflowname='{0}' and wf.workflowrevision='{1}' ",
                        para("workflowname"), para("workflowrevision"))
        sb.AppendLine("order by wfs.sequence")
        GetStepInfo.DBTable = dal.StandardGetDataTable(sb.ToString)
    End Function

    Function UpdateStepWorkTime(ByVal dtstep As DataTable) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateStepWorkTime
        UpdateStepWorkTime = dal.UpdateStepWorkTime(dtstep)
    End Function
#End Region

#Region "获取Workflow的工序列表"
    Public Function GetSpecListByWorkflowID(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetSpecListByWorkflowID
        Dim strWorkflowID As String = para("WorkflowID").ToString

        '       Dim strQuery As String = " select wfs.subworkflowbaseid,wfs.subworkflowid,s.specid,sb.specname,s.iscooperation,s.specrevision,sb1.specname as specname1,"
        's1.specid as specid1,s1.specrevision as specversion1,s1.iscooperation as iscooperation1 from  workflowstep wfs
        '  left join spec s on s.specid=wfs.specid
        '  left join specbase sb on sb.specbaseid=s.specbaseid
        '  left join specbase sb1 on sb1.specbaseid=wfs.specbaseid
        '  left join spec s1 on s1.specid=sb1.revofrcdid 
        '  WHERE wfs.workflowid = '4800268000000099'
        '  ORDER BY wfs.sequence"


        Dim strQuery As String = "SELECT sb.specname,s.specrevision,s.specid,wfss.sequence," & vbCrLf & _
                                "   (CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey" & vbCrLf & _
                                "FROM spec s" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "RIGHT JOIN" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT wfs.sequence," & vbCrLf & _
                                "       (CASE WHEN wfs.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = wfs.specbaseid) ELSE specid END) AS specid" & vbCrLf & _
                                "   FROM workflowstep wfs" & vbCrLf & _
                                "   WHERE wfs.workflowid = '" & strWorkflowID & "'" & vbCrLf & _
                                "   ORDER BY wfs.sequence" & vbCrLf & _
                                ") wfss ON s.specid = wfss.specid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf
        Dim strType As String = para("OprType").ToString
        If strType = "MultiDispatch" Then
            strQuery += "AND s.iscooperation = 2 OR s.iscooperation IS NULL" & vbCrLf
        ElseIf strType = "DataPointInput" Then

        End If

        ''排除检验工序
        Dim strCheckSpecKey As String = para("CheckSpecKey").ToString
        If strCheckSpecKey <> "" Then
            Dim arrayS As String() = strCheckSpecKey.Split(",")
            If arrayS.Length > 0 Then
                For i As Integer = 0 To arrayS.Length - 1
                    strQuery += "AND sb.specname NOT LIKE '%" & arrayS(i).ToString & "%'" & vbCrLf
                Next
            End If
        End If

        ''排除清洗工序
        Dim strCleanSpecKey As String = para("CleanSpecKey").ToString
        If strCleanSpecKey <> "" Then
            Dim arrayC As String() = strCleanSpecKey.Split(",")
            If arrayC.Length > 0 Then
                For i As Integer = 0 To arrayC.Length - 1
                    strQuery += "   AND sb.specname NOT LIKE '%" & arrayC(i).ToString & "%'" & vbCrLf
                Next
            End If
        End If

        strQuery += "ORDER BY wfss.sequence ASC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetSpecListByWorkflowID(strQuery)

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result


    End Function
#End Region

#Region "获取工段的生产单元列表"
    Function GetTeamListByWorkCenter(ByVal strWorkCenterName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetTeamListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetTeamListByWorkCenter(strWorkCenterName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取车间的工段列表"
    Function GetWorkCenterListByFactory(ByVal strFactoryName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkCenterListByFactory
        Dim strQuery As String = "SELECT wc.workcentername,wc.workcenterid" & vbCrLf & _
                                "FROM workcenter wc" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = wc.factoryid" & vbCrLf & _
                                "WHERE f.factoryname = '" & strFactoryName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkCenterListByFactory(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据批次ID列表获取工序派工信息"
    Function GetSpecDispatchByContainerList(ByVal dtContainerList As DataTable) As uMESProcessResult Implements uMESDispatchIBusiness.GetSpecDispatchByContainerList
        Dim result As New uMESProcessResult

        Try
            ''批次ID列表
            Dim strContainerList As String = ""
            For i As Integer = 0 To dtContainerList.Rows.Count - 1
                Dim strContainerID As String = dtContainerList.Rows(i)("ContainerID").ToString
                If strContainerList = "" Then
                    strContainerList = "'" & strContainerID & "'"
                Else
                    strContainerList += ",'" & strContainerID & "'"
                End If
            Next

            Dim strQuery As String = ""
            ''获取批次在各工序已派工的数量
            strQuery = "SELECT SUM(wcd.qty) AS dispatchedqty,wcd.containerid,wcd.specid" & vbCrLf & _
                        "FROM workcenterdispatching wcd" & vbCrLf & _
                        "WHERE wcd.containerid IN (" & strContainerList & ")" & vbCrLf & _
                        "GROUP BY wcd.containerid,wcd.specid" & vbCrLf

            Dim dtQty As DataTable = dal.GetSpecDispatchByContainerList(strQuery)
            dtQty.TableName = "dtQty"

            ''获取批次在各工序已派工的零件编号
            strQuery = "SELECT wcdc.childcontainer,wcdc.sequence,wcdc.workcenterdispatchingid,wcd.containerid,wcd.specid" & vbCrLf & _
                        "FROM workcenterdispatchingchildcont wcdc" & vbCrLf & _
                        "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wcdc.workcenterdispatchingid" & vbCrLf & _
                        "WHERE wcd.containerid IN (" & strContainerList & ")" & vbCrLf

            Dim dtDispatchedChild As DataTable = dal.GetSpecDispatchByContainerList(strQuery)
            dtDispatchedChild.TableName = "dtDispatchedChild"

            ''获取批次的所有零件编号
            strQuery = "SELECT c.containername,c.parentcontainerid" & vbCrLf & _
                        "FROM container c" & vbCrLf & _
                        "WHERE c.status = 1 AND c.parentcontainerid IN (" & strContainerList & ")" & vbCrLf

            Dim dtAllChild As DataTable = dal.GetSpecDispatchByContainerList(strQuery)
            dtAllChild.TableName = "dtAllChild"

            Dim ds As New DataSet
            ds.Tables.Add(dtQty)
            ds.Tables.Add(dtDispatchedChild)
            ds.Tables.Add(dtAllChild)

            result.DBDataSet = ds
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据派工单ID列表获取已报工的零件编号"
    Function GetReportedProductNoByWCDList(ByVal dtWCDList As DataTable) As uMESProcessResult Implements uMESDispatchIBusiness.GetReportedProductNoByWCDList
        Dim result As New uMESProcessResult

        Try
            ''派工单ID列表
            Dim strWCDList As String = ""
            For i As Integer = 0 To dtWCDList.Rows.Count - 1
                Dim strWCDID As String = dtWCDList.Rows(i)("WorkCenterDispatchingID").ToString
                If strWCDList = "" Then
                    strWCDList = "'" & strWCDID & "'"
                Else
                    strWCDList += ",'" & strWCDID & "'"
                End If
            Next

            Dim strQuery As String = ""
            ''获取派工单所有的零件编号
            strQuery = "SELECT wcdc.childcontainer,wcdc.sequence,wcdc.workcenterdispatchingid" & vbCrLf & _
                       "FROM workcenterdispatchingchildcont wcdc" & vbCrLf & _
                       "LEFT JOIN container c ON c.containername = wcdc.childcontainer" & vbCrLf & _
                       "WHERE c.status = 1" & vbCrLf & _
                       "AND wcdc.workcenterdispatchingid IN (" & strWCDList & ")" & vbCrLf

            Dim dtAllChild As DataTable = dal.GetProductNoByWorkCenterDispatch(strQuery)
            dtAllChild.TableName = "dtAllChild"

            ''获取派工单已报工的零件编号
            strQuery = "SELECT wrhc.childcontainer,wrhc.sequence,wrh.workcenterdispatchingid" & vbCrLf & _
                       "FROM workingreporthistorychildconta wrhc" & vbCrLf & _
                       "LEFT JOIN workingreporthistory wrh ON wrh.workingreporthistoryid = wrhc.workingreporthistoryid" & vbCrLf & _
                       "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                       "WHERE hml.reversalstatus = 1" & vbCrLf & _
                       "AND wrh.workcenterdispatchingid IN (" & strWCDList & ")" & vbCrLf

            Dim dtReportedChild As DataTable = dal.GetProductNoByWorkCenterDispatch(strQuery)
            dtReportedChild.TableName = "dtReportedChild"

            Dim ds As New DataSet
            ds.Tables.Add(dtAllChild)
            ds.Tables.Add(dtReportedChild)

            result.DBDataSet = ds
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段的工人列表"
    Function GetEmployeeListByWorkCenter(ByVal strWorkCenterName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetEmployeeListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim app As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()

            Dim strCheck1SpecKey As String = app.GetValue("CheckRole", System.Type.GetType("System.String"))
            'Dim strCheck2SpecKey As String = app.GetValue("ProductCheckRole", System.Type.GetType("System.String"))

            Dim DT As DataTable = dal.GetEmployeeListByWorkCenter(strWorkCenterName)
            Dim returnDT As DataTable = DT.Clone()
            Dim strNameList As List(Of String) = New List(Of String)


            For Each row As DataRow In DT.Rows
                If row("rolename").ToString().Trim() = strCheck1SpecKey Then
                    strNameList.Add(row("employeeid").ToString())
                End If
            Next

            Dim dt1 As DataTable = DT.Clone()

            For Each row As DataRow In DT.Rows
                Dim isHave As Boolean = False
                For i As Integer = 0 To strNameList.Count - 1
                    If row("employeeid").ToString().Trim() = strNameList(i) Then
                        isHave = True
                        Exit For
                    End If
                Next
                If isHave = False Then
                    dt1.ImportRow(row)
                End If
            Next

            For Each row As DataRow In dt1.Rows
                Dim strFilter As String = String.Format("employeeid='{0}'", row("employeeid").ToString())
                Dim rows As DataRow() = returnDT.Select(strFilter)
                If rows.Length = 0 Then
                    returnDT.ImportRow(row)
                End If
            Next
            result.Result = True
            result.DBTable = returnDT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取车间的互检人列表"
    Function GetMutualCheckEmployeeList(ByVal strFactoryName As String, ByVal strKey As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetMutualCheckEmployeeList
        Dim strQuery As String = "SELECT e.employeename,e.employeeid,e.fullname" & vbCrLf & _
                                "FROM employeerole er" & vbCrLf & _
                                "LEFT JOIN roledef r ON r.roleid = er.roleid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = er.employeeid" & vbCrLf & _
                                "LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = sv.factoryid" & vbCrLf & _
                                "WHERE r.rolename = '" & strKey & "'" & vbCrLf & _
                                "AND f.factoryname = '" & strFactoryName & "'" & vbCrLf & _
                                "GROUP BY e.employeename,e.employeeid,e.fullname" & vbCrLf & _
                                "ORDER BY e.fullname ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMutualCheckEmployeeList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段的设备列表"
    Function GetResourceListByWorkCenter(ByVal strWorkCenterName As String, ByVal strFactoryName As String, ByVal strEmp As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetResourceListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetResourceListByWorkCenter(strWorkCenterName, strFactoryName, strEmp)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Function GetResourceListByWorkCenter(ByVal strWorkCenterName As String, ByVal strFactoryName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetResourceListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetResourceListByWorkCenter(strWorkCenterName, strFactoryName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取生产单元的工人列表"
    Function GetEmployeeListByTeam(ByVal strTeamName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetEmployeeListByTeam
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetEmployeeListByTeam(strTeamName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取生产单元的设备列表"
    Function GetResourceListByTeam(ByVal strTeamName As String, ByVal strFactoryName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetResourceListByTeam
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetResourceListByTeam(strTeamName, strFactoryName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段派到小组的派工信息"
    Function GetWorkCenterDispatchingList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkCenterDispatchingList
        Dim strQuery As String = "SELECT td.*," & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT SUM(wrh.qty) FROM workingreporthistory wrh" & vbCrLf & _
                                "   LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "   LEFT jOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "   WHERE hml.reversalstatus = 1 AND wcd.workcenterdispatchingid1 = td.workcenterdispatchingid" & vbCrLf & _
                                ") AS ReportedQty" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT mo.mfgordername,c.containername,pb.productname,p.description,p.productrevision,c.originalqty,c.qty,pc.prioritycodename," & vbCrLf & _
                                "       sb1.specname AS dispatchspecname,wcd.qty AS dispatchqty,wcd.plannedstartdate,wcd.plannedcompletiondate," & vbCrLf & _
                                "       sb.specname,s.specrevision,s1.specrevision AS dispatchspecrev,c.plannedstartdate AS cplannedstartdate,c.plannedcompletiondate AS cPlannedCompletionDate," & vbCrLf & _
                                "       wcd.workcenterdispatchingid,wcd.workcenterdispatchingname,pf.productfamilyname,pm.phasemarkname, c.containerid, " & vbCrLf & _
                                "       (SELECT SUM(qty) FROM workcenterdispatching WHERE workcenterdispatchingid1 = wcd.workcenterdispatchingid) AS DispatchedQty," & vbCrLf & _
                                "       (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "       (CASE WHEN s1.iskey = 1 THEN 'G' ELSE '' END) AS iskey" & vbCrLf & _
                                "   FROM workcenterdispatching wcd" & vbCrLf & _
                                "   LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN spec s1 ON s1.specid = wcd.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb1 ON sb1.specbaseid = s1.specbaseid" & vbCrLf & _
                                "   LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "   LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "   LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "   LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "   LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "   LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "   LEFT JOIN team t ON t.teamid = wcd.teamid" & vbCrLf & _
                                "   WHERE 1 = 1 AND wcd.dispatchtype = 0 AND wcd.dispatchtotype = 0 AND c.status = 1 AND c.containername <> c.containerid" & vbCrLf

        If para.Keys.Contains("TeamName") = True Then
            strQuery += "AND t.teamname = '" & para("TeamName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("SecretLevel") = True Then
            strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("PriorityCode") = True Then
            strQuery += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("DispatchSpec") = True Then
            strQuery += "AND LOWER(sb1.specname) = '" & para("DispatchSpec").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        strQuery += ") td" & vbCrLf & _
                    "WHERE (dispatchqty - (CASE WHEN dispatchedqty IS NULL THEN 0 ELSE dispatchedqty END)) > 0" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段长派到小组的派工信息"
    Function GetToTeamList(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetToTeamList
        Dim strQuery As String = "SELECT t.teamname,wcd.qty,e.employeename,e.fullname,wcd.dispatchdate,wcd.plannedstartdate," & vbCrLf & _
                                "   wcd.plannedcompletiondate,wcd.workcenterdispatchingname AS WCDName" & vbCrLf & _
                                "FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN team t ON t.teamid = wcd.teamid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "WHERE wcd.dispatchtype = 0 AND wcd.dispatchtotype = 0" & vbCrLf & _
                                "AND wcd.containerid = '" & strContainerID & "' AND wcd.specid = '" & strSpecID & "'" & vbCrLf & _
                                "ORDER BY wcd.dispatchdate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetToTeamList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派到设备的派工信息"
    Function GetToResourceList(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetToResourceList
        Dim strQuery As String = "SELECT (CASE WHEN wcd1.workcenterdispatchingname IS NULL THEN '派到设备' ELSE wcd1.workcenterdispatchingname END) AS WCDName," & vbCrLf & _
                                "   r.resourcename,r.resourceename,r.resourcenum,e.employeename AS mainemployeename,e.fullname AS mainfullname," & vbCrLf & _
                                "   e1.employeename AS minoremployeename,e1.fullname AS minorfullname,e2.employeename AS dispatchemployeename,e2.fullname AS dispatchfullname," & vbCrLf & _
                                "   wcd.qty,wcd.dispatchdate,wcd.plannedstartdate,wcd.plannedcompletiondate,wcd.workcenterdispatchingname," & vbCrLf & _
                                "   e3.employeename AS receiveemployeename,e3.fullname AS receivefullname,wcd.receivedate" & vbCrLf & _
                                "FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd1 ON wcd1.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wcd.resourceid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e1 ON e1.employeeid = wcd.minormfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e2 ON e2.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e3 ON e3.employeeid = wcd.receiveemployeeid" & vbCrLf & _
                                "WHERE wcd.dispatchtotype = 1" & vbCrLf & _
                                "AND wcd.containerid = '" & strContainerID & "' AND wcd.specid = '" & strSpecID & "'" & vbCrLf & _
                                "ORDER BY wcd.dispatchdate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetToResourceList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工信息"
    Function GetWorkingReportList(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkingReportList
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,e.employeename,e.fullname,wrh.qty,r.resourcename," & vbCrLf & _
                                "   r.resourceename,r.resourcenum,wrh.ncprogramname,hml.txndate,wrh.realcompletiondate,wrh.workingreporthistoryid" & vbCrLf & _
                                "FROM workingreporthistory wrh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wrh.resourceid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1" & vbCrLf & _
                                "AND wcd.containerid = '" & strContainerID & "' AND wcd.specid = '" & strSpecID & "'" & vbCrLf & _
                                "ORDER BY hml.txndate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkingReportList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取批次的质量记载五单信息"
    Function GetFiveBillsInfoByContainer(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetFiveBillsInfoByContainer
        Dim strQuery As String = "SELECT f.fivebillno,ft.fivebilltypename,f.conclusion" & vbCrLf & _
                                "FROM fivebillsinfo f" & vbCrLf & _
                                "LEFT JOIN fivebilltype ft ON ft.fivebilltypeid = f.fivebilltypeid" & vbCrLf & _
                                "LEFT JOIN qualityrecord q ON q.qualityrecordid = f.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = q.containerid" & vbCrLf & _
                                "WHERE c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "ORDER BY f.fivebillno ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetFiveBillsInfoByContainer(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工信息"
    Function GetWorkingReportListByWCDName(ByVal strWCDName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkingReportListByWCDName
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,e.employeename,e.fullname,wrh.qty,r.resourcename," & vbCrLf & _
                                "   r.resourceename,r.resourcenum,wrh.ncprogramname,hml.txndate,wrh.realcompletiondate,wrh.workingreporthistoryid," & vbCrLf & _
                                "   sb.specname,s.specrevision,s.specid,c.containername" & vbCrLf & _
                                "FROM workingreporthistory wrh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = hml.containerid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wrh.resourceid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1" & vbCrLf & _
                                "AND wcd.workcenterdispatchingname = '" & strWCDName & "'" & vbCrLf & _
                                "ORDER BY hml.txndate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkingReportList(strQuery)

            DT.Columns.Add("ViewProductNo")
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim strHypeLink As String = "<a href = '#' onclick = ""openProductNowins('" & DT.Rows(i)("WorkingReportHistoryID").ToString & "','" & DT.Rows(i)("ContainerName").ToString & "')"" style='font-size:12px; color:blue;'>查看零件编号</a>"
                DT.Rows(i)("ViewProductNo") = strHypeLink
            Next

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工信息"
    Function GetWorkingReportListByContainerName(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkingReportListByContainerName
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,e.employeename,e.fullname,wrh.qty,r.resourcename," & vbCrLf & _
                                "   r.resourceename,r.resourcenum,wrh.ncprogramname,hml.txndate,wrh.realcompletiondate,wrh.workingreporthistoryid," & vbCrLf & _
                                "   sb.specname,s.specrevision,s.specid" & vbCrLf & _
                                "FROM workingreporthistory wrh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wrh.resourceid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1" & vbCrLf & _
                                "AND c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "ORDER BY hml.txndate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkingReportList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取小组派到设备的派工信息"
    Function GetTeamDispatchingList(ByVal strWCDID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetTeamDispatchingList
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetTeamDispatchingByWCD(strWCDID)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段派工信息For改派"
    Function GetWorkCenterDispatchingForChange(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkCenterDispatchingForChange
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        Dim strProductName As String = ""
        If para.Keys.Contains("ContainerName") = True Then
            Dim strContainerName As String = para("ContainerName").ToString
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                    strProductName = r.DBTable.Rows(0)("productname").ToString().Trim()
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strQuery As String = "SELECT mo.mfgordername,c.containername,sb.specname,t.teamname,wcd.dispatchdate," & vbCrLf & _
                                "e.employeename AS mainmfgemployee,e.fullname AS mainfullname," & vbCrLf & _
                                "e1.employeename AS minormfgemployee,e1.fullname AS minorfullname," & vbCrLf & _
                                "r.resourcename,r.resourceename,r.resourcenum,e2.employeename AS dispatchemployee,e2.fullname AS dispatchfullname," & vbCrLf & _
                                "wcd.qty,wcd.reportedqty,wcd.plannedstartdate,wcd.plannedcompletiondate," & vbCrLf & _
                                "wcd.workcenterdispatchingname,pb.productname,p.description,c.OriginalQty,c.qty AS CurrentQty," & vbCrLf & _
                                "(CASE WHEN wcd.status = 0 THEN '已派工' WHEN wcd.status = 1 THEN '已接收' WHEN wcd.status = 2 THEN '已报工' WHEN wcd.status = 3 THEN '已完成' WHEN wcd.status = 4 THEN '已关闭' ELSE TO_CHAR(wcd.status) END) AS status," & vbCrLf & _
                                "wcd1.workcenterdispatchingname AS parentname,wcd.dispatchtotype,wcd.dispatchtype,pf.productfamilyname,pm.phasemarkname," & vbCrLf & _
                                "(CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold" & vbCrLf & _
                                "FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN team t ON t.teamid = wcd.teamid" & vbCrLf & _
                                "LEFT JOIN workcenter wc ON wc.workcenterid = wcd.workcenterid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wcd.resourceid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e1 ON e1.employeeid = wcd.minormfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e2 ON e2.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd1 ON wcd1.workcenterdispatchingid = wcd.workcenterdispatchingid1 " & vbCrLf & _
                                "WHERE  c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL " & vbCrLf

        If para.Keys.Contains("Status") = True Then
            strQuery += " AND wcd.status = " & para("Status").ToString & vbCrLf
        End If

        If para.Keys.Contains("HaveChild") = True Then
            Dim strHaveChild As String = para("HaveChild").ToString.ToLower
            If strHaveChild = "false" Then
                strQuery += " AND wcd.workcenterdispatchingid NOT IN (SELECT workcenterdispatchingid1 FROM workcenterdispatching WHERE workcenterdispatchingid1 IS NOT NULL)" & vbCrLf
            End If
        End If

        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND wc.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "AND c.containername  LIKE '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
            Else
                strQuery += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
                strQuery += "AND pb.productname = '" & strProductName & "' " & vbCrLf
            End If
        End If
        'If para.Keys.Contains("SecretLevel") = True Then
        '    strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        'End If
        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("SpecName") = True Then
            strQuery += "AND LOWER(sb.specname) LIKE '%" & para("SpecName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("specno") = True Then
            strQuery += "AND LOWER(s.specno) = '" & para("specno").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("TeamName") = True Then
            strQuery += "AND t.teamname = '" & para("TeamName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        strQuery += "ORDER BY pb.productname ASC,c.containername ASC" & vbCrLf & _
                    "" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            result.Result = True
            r.DBTable.Columns.Add("dispSpecname")
            For Each row As DataRow In r.DBTable.Rows
                row("dispSpecname") = CurrentInfo.ReplaceProductNameForSpec(row("specname"))
            Next

            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取小组派工信息For改派"
    Function GetTeamDispatchingForChange(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetTeamDispatchingForChange
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetTeamDispatchingForChange(para)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "创建派工单"
    Function CreateDispatching(ByVal para As DispatchingPara, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMESDispatchIBusiness.CreateDispatching
        Dim strDispatchType As DispatchTypeEnum.DispatchTypeEnum = para.DispatchType
        Dim dtChildContainer As DataTable = para.ChildContainer

        Dim dataEntityList As New List(Of ClientAPIEntity)

        ''派工单号
        Dim dataEntity As New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = Now.ToString("yyyyMMddHHmmssfff")
        dataEntityList.Add(dataEntity)

        ''跟踪卡号
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Container"
        dataEntity.ClientDataValue = para.ContainerName
        dataEntity.ClientOtherValue = "Lot"
        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        dataEntityList.Add(dataEntity)

        ''工序
        Dim s As RevisionedObjectRef = para.Spec
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Spec"
        dataEntity.ClientDataValue = s.__name
        dataEntity.ClientDataVersion = s.__rev
        dataEntity.ClientDataIsROR = s.__useROR
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntityList.Add(dataEntity)

        ''数量
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para.Qty
        dataEntityList.Add(dataEntity)

        ''计划开始日期
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para.PlannedStartDate
        dataEntityList.Add(dataEntity)

        ''计划完成日期
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para.PlannedCompletionDate
        dataEntityList.Add(dataEntity)

        ''派工者
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "DispatchEmployee"
        dataEntity.ClientDataValue = para.DispatchEmployee
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        ''派工时间
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "DispatchDate"
        dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
        dataEntityList.Add(dataEntity)

        ''DispatchToType
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "DispatchToType"
        dataEntity.ClientDataValue = para.DispatchToType
        dataEntityList.Add(dataEntity)

        ''工段
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "WorkCenter"
        dataEntity.ClientDataValue = para.WorkCenterName
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        ''工段长派工
        If strDispatchType = DispatchTypeEnum.DispatchTypeEnum.WorkCenterDispatch Then

            ''派工类型
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "DispatchType"
            dataEntity.ClientDataValue = "0"
            dataEntityList.Add(dataEntity)

            If para.DispatchToType = DispatchToTypeEnum.DispatchToTypeEnum.ToTeam Then

                ''Team
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Team"
                dataEntity.ClientDataValue = para.Team
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

            ElseIf para.DispatchToType = DispatchToTypeEnum.DispatchToTypeEnum.ToResource Then


                ' ''副岗
                'dataEntity = New ClientAPIEntity
                'dataEntity.ClientDataName = "MinorMfgEmployee"
                'dataEntity.ClientDataValue = para.MinorMfgEmployee
                'dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                'dataEntityList.Add(dataEntity)

                ''设备
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Resource"
                dataEntity.ClientDataValue = para.Resource
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

            End If

        ElseIf strDispatchType = DispatchTypeEnum.DispatchTypeEnum.TeamDispatch Then

            ''派工类型
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "DispatchType"
            dataEntity.ClientDataValue = "1"
            dataEntityList.Add(dataEntity)

            ' ''主岗
            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataName = "MainMfgEmployee"
            'dataEntity.ClientDataValue = para.MainMfgEmployee
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            'dataEntityList.Add(dataEntity)

            ' ''副岗
            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataName = "MinorMfgEmployee"
            'dataEntity.ClientDataValue = para.MinorMfgEmployee
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            'dataEntityList.Add(dataEntity)

            ''设备
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Resource"
            dataEntity.ClientDataValue = para.Resource
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)

            ''工段长派工单
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "WorkCenterDispatching"
            dataEntity.ClientDataValue = para.WorkCenterDispatching
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)

        End If

        ''主岗
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "MainMfgEmployee"
        dataEntity.ClientDataValue = para.MainMfgEmployee
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.CreateDispatching(dataEntityList, dtChildContainer, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result

    End Function
#End Region

#Region "修改派工单"
    Function UpdateDispatching(ByVal para As DispatchingPara, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateDispatching
        Dim strDispatchType As DispatchTypeEnum.DispatchTypeEnum = para.DispatchType
        Dim dtChildContainer As DataTable = para.ChildContainer
        Dim dtNewChildContainer As DataTable = para.NewChildContainer

        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        ''改派
        If para.OperationType = OperationTypeEnum.OperationTypeEnum.ChangeDispatch Then

            ''数量
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "Qty"
            dataEntity.ClientDataValue = para.Qty
            dataEntityList.Add(dataEntity)

            ''计划开始日期
            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            'dataEntity.ClientDataName = "PlannedStartDate"
            'dataEntity.ClientDataValue = para.PlannedStartDate
            'dataEntityList.Add(dataEntity)

            ''计划完成日期
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "PlannedCompletionDate"
            dataEntity.ClientDataValue = para.PlannedCompletionDate
            dataEntityList.Add(dataEntity)

            ''派工者
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "DispatchEmployee"
            dataEntity.ClientDataValue = para.DispatchEmployee
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)

            ''派工时间
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "DispatchDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntityList.Add(dataEntity)

            If para.DispatchToType = DispatchToTypeEnum.DispatchToTypeEnum.ToTeam Then

                ''Team
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Team"
                dataEntity.ClientDataValue = para.Team
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

            ElseIf para.DispatchToType = DispatchToTypeEnum.DispatchToTypeEnum.ToResource Then

                ''主岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "MainMfgEmployee"
                dataEntity.ClientDataValue = para.MainMfgEmployee
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''副岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "MinorMfgEmployee"
                dataEntity.ClientDataValue = para.MinorMfgEmployee
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''设备
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Resource"
                dataEntity.ClientDataValue = para.Resource
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

            End If

        ElseIf para.OperationType = OperationTypeEnum.OperationTypeEnum.Receive Then ''任务接收

            ''接收者
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ReceiveEmployee"
            dataEntity.ClientDataValue = para.ReceiveEmployee
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)

            ''接收时间
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "ReceiveDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntityList.Add(dataEntity)

            ''数量
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "Status"
            dataEntity.ClientDataValue = para.DispatchStatus
            dataEntityList.Add(dataEntity)
        ElseIf para.OperationType = OperationTypeEnum.OperationTypeEnum.Mark Then ''修改零件编号

        End If

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateDispatching(para.Name, dataEntityList, dtChildContainer, dtNewChildContainer, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Function UpdateDispatching(ByVal para As DispatchingPara) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateDispatching
        Dim strList As New ArrayList
        Dim strSQL As String = String.Format("update workcenterdispatching set ReportedQty={0},Status=3 where workcenterdispatchingname='{1}'",
                                   para.Qty, para.WorkCenterDispatching)
        strList.Add(strSQL)
        UpdateDispatching = dal.SaveDataInfo(strList)
    End Function

#End Region

#Region "获取派到设备/工人的派工信息"
    Function GetToEmployeeDispatchingList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetToEmployeeDispatchingList
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        If para.Keys.Contains("ContainerName") = True Then
            Dim strContainerName As String = para("ContainerName").ToString
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strQuery As String = "SELECT mt.*," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT fullname" & vbCrLf & _
                                "       FROM" & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT e.employeeid,e.employeename,e.fullname,wcd.containerid,wcd.specid" & vbCrLf & _
                                "           FROM workcenterdispatching wcd" & vbCrLf & _
                                "           LEFT JOIN employee e ON e.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "           WHERE wcd.dispatchtype = 0" & vbCrLf & _
                                "           ORDER BY wcd.dispatchdate ASC" & vbCrLf & _
                                "       ) wcd2" & vbCrLf & _
                                "       WHERE ROWNUM = 1 AND wcd2.containerid = mt.containerid AND wcd2.specid = mt.nextSpecid" & vbCrLf & _
                                "   ) AS nextDispatchEmployee" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "  SELECT mo.mfgordername,c.containername,pb.productname,p.productrevision,p.description,p.productid,c.originalqty,c.qty,pc.prioritycodename," & vbCrLf & _
                                "       sb1.specname AS dispatchspecname,wcd.qty AS dispatchqty,wcd.plannedstartdate,wcd.plannedcompletiondate," & vbCrLf & _
                                "       sb.specname,s.specrevision,s1.specrevision AS dispatchspecrev,c.plannedstartdate AS cplannedstartdate,c.plannedcompletiondate AS cPlannedCompletionDate," & vbCrLf & _
                                "       wcd.workcenterdispatchingid,wcd.workcenterdispatchingname,r.resourcename,r.resourcenum,r.resourceename," & vbCrLf & _
                                "       e.employeename AS mainemployee,e.fullname AS mainfullname," & vbCrLf & _
                                "       e1.employeename AS minoremployee,e1.fullname AS minorfullname,wcd.reportedqty," & vbCrLf & _
                                "       DECODE(wcd.status,'0','已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS status" & vbCrLf & _
                                "       ,c.containerid,wcd.specid,pf.productfamilyname,pm.phasemarkname,c.togetherflag," & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           CASE WHEN" & vbCrLf & _
                                "               (" & vbCrLf & _
                                "                   SELECT qr.qualityrecordid FROM productnoinfo pni" & vbCrLf & _
                                "                   LEFT JOIN qualityrecord qr ON qr.qualityrecordid = pni.qualityrecordid" & vbCrLf & _
                                "                   WHERE ',' || pni.containerlist || ',' LIKE '%,' || sb.specname || ',%' AND ROWNUM = 1 AND qr.containerid = c.containerid" & vbCrLf & _
                                "               ) IS NULL THEN '0' ELSE '1' END" & vbCrLf & _
                                "       ) AS chaochachuli," & vbCrLf & _
                                "       (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "       (CASE WHEN s1.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "       (" & vbCrLf & _
                                "          CASE WHEN" & vbCrLf & _
                                "              (" & vbCrLf & _
                                "                  SELECT SUM(wcd1.reportedqty) FROM workcenterdispatching wcd1 WHERE wcd1.containerid = wcd.containerid AND wcd1.specid = wcd.specid" & vbCrLf & _
                                "              ) IS NULL THEN 0 ELSE" & vbCrLf & _
                                "              (" & vbCrLf & _
                                "                  SELECT SUM(wcd1.reportedqty) FROM workcenterdispatching wcd1 WHERE wcd1.containerid = wcd.containerid AND wcd1.specid = wcd.specid" & vbCrLf & _
                                "              ) END" & vbCrLf & _
                                "       ) AS contreportedqty," & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT wfs2.specid" & vbCrLf & _
                                "           FROM" & vbCrLf & _
                                "               (" & vbCrLf & _
                                "                   SELECT wfs1.workflowid,wfs1.sequence,(CASE WHEN wfs1.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wfs1.specbaseid) ELSE wfs1.specid END) AS specid" & vbCrLf & _
                                "                   FROM workflowstep wfs1" & vbCrLf & _
                                "                   ORDER BY wfs1.sequence ASC" & vbCrLf & _
                                "               ) wfs2" & vbCrLf & _
                                "           WHERE ROWNUM = 1 AND wfs2.sequence > wfs.sequence AND wfs2.workflowid = wfs.workflowid" & vbCrLf & _
                                "       ) AS nextSpecid," & vbCrLf & _
                                "       ROWNUM AS rowIndex" & vbCrLf & _
                                "   FROM workcenterdispatching wcd" & vbCrLf & _
                                "   LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN spec s1 ON s1.specid = wcd.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb1 ON sb1.specbaseid = s1.specbaseid" & vbCrLf & _
                                "   LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "   LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "   LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "   LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "   LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "   LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "   LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "   LEFT JOIN employee e1 ON e1.employeeid = wcd.minormfgemployeeid" & vbCrLf & _
                                "   LEFT JOIN resourcedef r ON r.resourceid = wcd.resourceid" & vbCrLf & _
                                "   LEFT JOIN workcenter w ON w.workcenterid = wcd.workcenterid" & vbCrLf & _
                                "   WHERE 1 = 1 AND wcd.status <> 3 AND wcd.status <> 4 AND c.status = 1 AND c.containername <> c.containerid" & vbCrLf & _
                                "   AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "   AND wcd.specid = cu.specid" & vbCrLf   'AND wcd.dispatchtotype = 1 

        If para.Keys.Contains("WorkCenterName") = True Then
            'strQuery += "AND w.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
            Else
                strQuery += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            End If
        End If
        If para.Keys.Contains("SecretLevel") = True Then
            strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("PriorityCode") = True Then
            strQuery += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("DispatchSpec") = True Then
            strQuery += "AND LOWER(sb1.specname) LIKE '%" & para("DispatchSpec").ToString.ToLower & "%'" & vbCrLf
        End If

        ''区分普通工人、清洗工人、配套出库工人
        If para.Keys.Contains("WorkerType") = True Then
            Dim strWorkerType As String = para("WorkerType").ToString
            Dim strWorkerTypeKey As String = para("WorkerTypeKey").ToString
            Dim array As String() = strWorkerTypeKey.Split(",")

            Select Case strWorkerType
                Case "Normal" ''普通工人
                    If array.Length > 0 Then
                        For i As Integer = 0 To array.Length - 1
                            If array(i).Trim() <> "" Then
                                strQuery += "AND LOWER(sb1.specname) NOT LIKE '%" & array(i).ToLower & "%'" & vbCrLf
                            End If
                        Next
                    End If
                Case "CleanWorker" ''清洗工人
                    If array.Length > 0 Then
                        strQuery += "AND (" & vbCrLf
                        For i As Integer = 0 To array.Length - 1
                            If i = 0 Then
                                strQuery += "   (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                            Else
                                strQuery += "   OR (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                            End If
                        Next
                        strQuery += ")" & vbCrLf
                    End If
                Case "MaterialOutWorker" ''配套出库工人
                    'If array.Length > 0 Then
                    '    strQuery += "AND (" & vbCrLf
                    '    For i As Integer = 0 To array.Length - 1

                    '        If i = 0 Then
                    '            strQuery += "   (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                    '        Else
                    '            strQuery += "   OR (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                    '        End If
                    '    Next
                    '    strQuery += ")" & vbCrLf
                    'End If
            End Select
        End If

        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EmployeeName") = True Then
            If para.Keys.Contains("SearchType") And para("WorkerType") <> "CleanWorker" Then
                Dim strSearchType As String = para("SearchType").ToString
                If strSearchType = "Init" Then

                    strQuery += "AND (LOWER(e.employeename) = LOWER('" & para("EmployeeName").ToString & "') OR LOWER(e1.employeename) = LOWER('" & para("EmployeeName").ToString & "'))" & vbCrLf
                    'If strWorkerType = "Normal" Then

                    'ElseIf strWorkerType = "Normal" Then

                    'End If
                Else
                    strQuery += "AND (LOWER(e.employeename) = LOWER('" & para("EmployeeName").ToString & "') OR LOWER(e1.employeename) = LOWER('" & para("EmployeeName").ToString & "')" & vbCrLf & _
                                "   OR LOWER('" & para("EmployeeName").ToString & "') IN (" & vbCrLf & _
                                "       SELECT LOWER(er.employeename)" & vbCrLf & _
                                "       FROM employeeresourcelist erl" & vbCrLf & _
                                "       LEFT JOIN employee er ON er.employeeid = erl.employeeid" & vbCrLf & _
                                "       LEFT JOIN resourcedef re ON re.resourceid = erl.resourcelistid" & vbCrLf & _
                                "       WHERE erl.resourcelistid = wcd.resourceid))" & vbCrLf
                End If
            Else
                ''不做处理
            End If
        End If

        Dim intCurrentPageIndex As Integer = Convert.ToInt32(para("CurrentPageIndex").ToString)
        Dim intPageSize As Integer = Convert.ToInt32(para("PageSize").ToString)

        strQuery += "ORDER BY c.containername DESC" & vbCrLf & _
                    ") mt" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim dtAll As DataTable = dal.GetToEmployeeDispatchingList(strQuery)

            strQuery += "WHERE rowIndex BETWEEN " & intPageSize * (intCurrentPageIndex - 1) + 1 & " AND " & intPageSize * intCurrentPageIndex & vbCrLf
            Dim dtCurrentPage As DataTable = dal.GetToEmployeeDispatchingList(strQuery)

            Dim DT As DataTable = dtCurrentPage
            DT.Columns.Add("ViewQuality")
            DT.Columns.Add("ViewSetup")
            DT.Columns.Add("IsCC")
            DT.Columns.Add("NextDispatch")
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim strHypeLink As String = "<a href = '#' onclick = ""OpenWindow('',500,740)"" style='font-size:12px; color:blue;'>质量记载查看</a>"
                DT.Rows(i)("ViewQuality") = strHypeLink
                strHypeLink = "<a href = '#' onclick = ""ViewSetUp('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("SpecID").ToString & "')"" style='font-size:12px; color:blue;'>查看生产准备</a>"
                DT.Rows(i)("ViewSetup") = strHypeLink

                Dim strCC As String = DT.Rows(i)("ChaoChaChuLi").ToString
                Dim strImg As String = ""
                If strCC = "1" Then
                    strImg = "<a href = '#' title=""点击查看零件编号"" onclick = ""ViewProductNo('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("SpecID").ToString & "')""><img src=""../../../images/Red16.png"" style=""border:none;"" /></a>"
                End If
                strHypeLink = strImg
                DT.Rows(i)("IsCC") = strHypeLink
                If Not IsDBNull(DT.Rows(i)("nextDispatchEmployee")) Then
                    strHypeLink = "<a href = '#' onclick = ""ViewDispatchAndReport('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("NextSpecID").ToString & "')"" style='font-size:12px; color:blue;'>" & DT.Rows(i)("nextDispatchEmployee").ToString & "</a>"
                    DT.Rows(i)("NextDispatch") = strHypeLink
                Else
                    DT.Rows(i)("NextDispatch") = ""
                End If
            Next

            result.Result = True
            result.DBTable = DT
            result.RowCount = dtAll.Rows.Count
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派到设备/工人的派工信息"
    Function GetToEmployeeDispatchingListByNew(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetToEmployeeDispatchingListByNew
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        Dim strContainerName As String = ""
        Dim strProductName As String = ""
        Dim strSpecID As String = ""
        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

        'If para.Keys.Contains("ContainerName") = False Then
        '    para.Add("ContainerName", "A101S10.12.9201/1606001")
        'End If

        If para.Keys.Contains("ContainerName") = True Then
            strContainerName = commBusiness.ReplaceContainerName(para("ContainerName").ToString.Trim())
            If strContainerName.Trim() <> "" Then
                Dim r1 As uMESProcessResult = GetContainerInfo(strContainerName)
                If r1.Result = True Then
                    Try
                        strTogetherFlag = r1.DBTable.Rows(0)("TogetherFlag").ToString
                        strProductName = r1.DBTable.Rows(0)("productname").ToString
                        strSpecID = r1.DBTable.Rows(0)("specid").ToString
                    Catch ex As Exception
                        strTogetherFlag = ""
                    End Try
                End If
            End If


        End If
        Dim strQuery As String = "select c.containername,c.qty,c.originalqty,sb.specname,pb.productname,p.productrevision,p.description,s.specrevision,e.fullname," & vbCrLf & _
                                 "e.employeename, t.workcenterdispatchingid,t.workcenterdispatchingname,t.containerid,t.mainmfgemployeeid,t.qty as DispatchQty,t.reportedqty,t.resourceid," & vbCrLf & _
                                 "t.specid,s1.specid,sb1.revofrcdid,t.status,t.workcenterid,w.workcentername,r.resourceename,r.resourcename,cs.workflowstepid,t.plannedcompletiondate from workcenterdispatching t " & vbCrLf & _
                                 "left join container c on c.containerid=t.containerid " & vbCrLf & _
                                 "left join spec s on s.specid=t.specid " & vbCrLf & _
                                 "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                                 "left join product p on p.productid=c.productid " & vbCrLf & _
                                 "left join productbase pb on  pb.productbaseid=p.productbaseid " & vbCrLf & _
                                 "left join currentstatus cs on cs.currentstatusid=c.currentstatusid " & vbCrLf & _
                                 "left join workflowstep wt on wt.workflowstepid=cs.workflowstepid " & vbCrLf & _
                                 "left join spec s1 on s1.specid=wt.specid " & vbCrLf & _
                                 "left join specbase sb1 on sb1.specbaseid=wt.specbaseid " & vbCrLf & _
                                 "left join employee e on e.employeeid=t.mainmfgemployeeid " & vbCrLf & _
                                 "left join workcenter w on w.workcenterid=t.workcenterid " & vbCrLf & _
                                 "left join resourcedef r on r.resourceid=t.resourceid " & vbCrLf & _
                                 "where (t.specid=s1.specid or t.specid=sb1.revofrcdid) and t.status<>3 AND c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL  "

        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "and LOWER(c.containername) = '" & strContainerName.ToLower & "'" & vbCrLf
            Else
                strQuery += "and LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
                strQuery += "and LOWER(pb.productname) = '" & strProductName.ToLower & "'" & vbCrLf
                strQuery += "and t.specid='" & strSpecID & "'" & vbCrLf
            End If
        End If

        If para.Keys.Contains("EmployeeName") = True Then
            strQuery += "  and e.employeename='" & para("EmployeeName").ToString().Trim() & "'"
        End If

        strQuery += " ORDER BY  c.containername,sb.specname"

        Dim result As New uMESProcessResult
        Dim dtAll As DataTable = dal.StandardGetDataTable(strQuery)
        dtAll.Columns.Add("DispatchSpecName")

        For Each row As DataRow In dtAll.Rows
            row("DispatchSpecName") = CurrentInfo.ReplaceProductNameForSpec(row("specname").ToString().Trim())
        Next

        Dim dt As DataTable = dtAll.Clone()
        Dim dispDT As DataTable = dtAll.Clone()

        If dtAll.Rows.Count > 0 Then
            Dim strFilter As String = String.Format("containername='{0}'", strContainerName)
            Dim rowsTmp As DataRow() = dtAll.Select(strFilter)
            If rowsTmp.Length > 0 Then
                Dim strSpecName As String = rowsTmp(0)("specname").ToString().Trim()
                For Each row As DataRow In dtAll.Rows
                    If row("specname").ToString().Trim() = strSpecName Then
                        dt.ImportRow(row)
                    End If
                Next
            Else
                dt = dtAll.Copy()
            End If
        Else
            dt = dtAll.Copy()
        End If
        result.Result = True
        result.DBTable = dt
        result.RowCount = dt.Rows.Count
        Return result
    End Function
#End Region

#Region "获取转工序处理的零件编号"
    Function GetCCProductNo(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetCCProductNo
        Dim strQuery As String = "SELECT pi.productno,pi.containerlist,qr.containerid,c.containername" & vbCrLf & _
                                "FROM productnoinfo pi" & vbCrLf & _
                                "LEFT JOIN qualityrecord qr ON qr.qualityrecordid = pi.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = qr.specid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = qr.containerid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf & _
                                "AND qr.containerid = '" & strContainerID & "'" & vbCrLf & _
                                "AND ',' || pi.containerlist || ',' LIKE '%,' ||" & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT sb.specname" & vbCrLf & _
                                "       FROM spec s" & vbCrLf & _
                                "       LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "       WHERE s.specid = '" & strSpecID & "'" & vbCrLf & _
                                ")" & vbCrLf & _
                                "|| ',%'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoForMark(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡的零件编号"
    Function GetProductNoForMark(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoForMark
        Dim strQuery As String = "SELECT c.containername," & vbCrLf & _
                                "   (CASE WHEN c.status = 0 THEN '已删除' WHEN c.status = 1 THEN '正常'  WHEN c.status = 2 THEN '已关闭'  WHEN c.status = 3 THEN 'Shipped'  WHEN c.status = 4 THEN '已消耗' END) AS status" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN container c1 ON c1.containerid = c.parentcontainerid" & vbCrLf & _
                                "WHERE c1.containername = '" & strContainerName & "'" & vbCrLf & _
                                "ORDER BY c.containername asc " & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoForMark(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据物料名称获取合格证号列表"
    Function GetQualificationListByMaterialName(ByVal strMaterialName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetQualificationListByMaterialName
        Dim strQuery As String = "SELECT m.materialinfoname FROM materialinfo m" & vbCrLf & _
                                "WHERE m.qualificationnum IS NOT NULL AND m.materialname = '" & strMaterialName & "'" & vbCrLf & _
                                "GROUP BY m.materialinfoname" & vbCrLf & _
                                "ORDER BY m.materialinfoname ASC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetQualificationListByMaterialName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派工单的零件编号"
    Function GetProductNoByDispatch(ByVal strDispachName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoByDispatch
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,c.childcontainer FROM workcenterdispatchingchildcont c" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = c.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN container cc ON cc.containername = c.childcontainer" & vbCrLf & _
                                "WHERE cc.status = 1 AND wcd.workcenterdispatchingname = '" & strDispachName & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByDispach(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工单的零件编号"
    Function GetProductNoByReport(ByVal strReportID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoByReport
        Dim strQuery As String = "SELECT childcontainer FROM workingreporthistorychildconta" & vbCrLf & _
                                "WHERE workingreporthistoryid = '" & strReportID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByReport(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取指定批次指定工序上的已派工零件编号"
    Function GetProductNoByContainerAndSpec(ByVal strContainerName As String, ByVal strSpecID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoByContainerAndSpec
        Dim strQuery As String = "SELECT c.childcontainer FROM workcenterdispatchingchildcont c" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = c.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN container cc on cc.containerid = wcd.containerid" & vbCrLf & _
                                "WHERE wcd.status <> 4 AND cc.containername = '" & strContainerName & "' AND wcd.specid = '" & strSpecID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByContainerAndSpec(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派到生产单元的派工单已派工零件编号"
    Function GetProductNoByWorkCenterDispatch(ByVal strWCDName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoByWorkCenterDispatch
        Dim strQuery As String = "SELECT c.childcontainer FROM workcenterdispatchingchildcont c" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = c.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching cc on cc.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "WHERE cc.workcenterdispatchingname = '" & strWCDName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByWorkCenterDispatch(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派工单已报工的零件编号"
    Function GetReportedProductNoByDispatch(ByVal strWCDName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetReportedProductNoByDispatch
        Dim strQuery As String = "SELECT wc.childcontainer,w.workingreporthistoryid FROM workingreporthistorychildconta wc" & vbCrLf & _
                                "LEFT JOIN workingreporthistory w ON w.workingreporthistoryid = wc.workingreporthistoryid" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = w.historymainlineid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = w.workcenterdispatchingid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1 AND wcd.workcenterdispatchingname = '" & strWCDName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetReportedProductNoByDispatch(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取到小组的派工单对应的子派工单"
    Function GetDispatchingByParent(ByVal strParentName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetDispatchingByParent
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd1 ON wcd1.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "WHERE wcd1.workcenterdispatchingname = '" & strParentName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetDispatchingByParent(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据设备获取人员列表"
    Function GetEmployeeListByResourceName(ByVal strResourceName As String, ByVal strWorkCenterName As String, ByVal strTeamName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetEmployeeListByResourceName
        'Dim strQuery As String = "SELECT e.employeeid,e.employeename,e.fullname" & vbCrLf & _
        '                        "FROM employee e" & vbCrLf & _
        '                        "LEFT JOIN resourcedef r ON r.resourceid = e.resourceid" & vbCrLf & _
        '                        "WHERE r.resourcename = '" & strResourceName & "'" & vbCrLf

        Dim strQuery As String = "SELECT e.employeeid,e.employeename,e.fullname" & vbCrLf & _
                                "FROM employee e" & vbCrLf & _
                                "LEFT JOIN employeeresourcelist erl ON erl.employeeid = e.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = erl.resourcelistid" & vbCrLf

        If strWorkCenterName <> "" Then
            strQuery += "LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid" & vbCrLf & _
                        "LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid" & vbCrLf
        End If

        If strTeamName <> "" Then
            strQuery += "LEFT JOIN teamemployee te ON te.employeeid = e.employeeid" & vbCrLf & _
                        "LEFT JOIN team t ON t.teamid = te.teamid" & vbCrLf
        End If

        strQuery += "WHERE 1 = 1" & vbCrLf

        If strWorkCenterName <> "" Then
            strQuery += "AND wc.workcentername = '" & strWorkCenterName & "'" & vbCrLf
        End If

        If strTeamName <> "" Then
            strQuery += "AND t.teamname = '" & strTeamName & "'" & vbCrLf
        End If

        strQuery += "AND r.resourcename = '" & strResourceName & "'" & vbCrLf & _
                    "GROUP BY e.employeeid,e.employeename,e.fullname" & vbCrLf & _
                    "ORDER BY e.fullname DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetEmployeeListByResourceName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取生产订单列表"
    Function GetMfgOrderList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetMfgOrderList

        Dim strQuery As String = "select  t.family,t.fiveweekname,t.fiveweeknote,t.fiveweekqty,t.fiveweekspecid," & vbCrLf &
        "t.fourweekname, t.fourweeknote, t.fourweekqty, t.fourweekspecid, t.threeweekname, t.threeweeknote," & vbCrLf &
        "t.threeweekqty, t.threeweekspecid, t.twoweekname, t.twoweeknote, t.twoweekqty, t.twoweekspecid," & vbCrLf &
        "t.oneweekname, t.oneweeknote, t.oneweekqty, t.oneweekspecid, t.mfgplanformonthid, t.mfgplanformonthname, " & vbCrLf &
        "pb.productname,p.productrevision,w.workflowrevision ,wb.workflowname,wb1.workflowname as workflowname1 ," & vbCrLf &
                                 "w1.workflowrevision as workflowrevisoin1," & vbCrLf &
                                 "sb1.specname as onespecname ,sb2.specname as twospecname ,sb3.specname as treespecname,sb4.specname as fourspecname," & vbCrLf &
                                 "sb5.specname as fivespecname   from MfgPlanForMonth t" & vbCrLf &
                                 "left join productbase pb on pb.productname=t.productname " & vbCrLf &
                                 "left join product p on p.productid=pb.revofrcdid " & vbCrLf &
                                 "left join workflow w on w.workflowid=p.workflowid " & vbCrLf &
                                 "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf &
                                 "left join workflowbase wb1 on wb1.workflowbaseid=p.workflowbaseid  " & vbCrLf &
                                 "left join workflow w1 on w1.workflowid=wb1.revofrcdid " & vbCrLf &
                                 "left join spec s1 on s1.specid=t.oneweekspecid " & vbCrLf &
                                 "left join specbase sb1 on sb1.specbaseid=s1.specbaseid " & vbCrLf &
                                 "left join spec s2 on s2.specid=t.twoweekspecid " & vbCrLf &
                                 "left join specbase sb2 on sb2.specbaseid=s2.specbaseid " & vbCrLf &
                                 "left join spec s3 on s3.specid=t.threeweekspecid " & vbCrLf &
                                 "left join specbase sb3 on sb3.specbaseid=s3.specbaseid " & vbCrLf &
                                 "left join spec s4 on s4.specid=t.fourweekspecid " & vbCrLf &
                                 "left join specbase sb4 on sb4.specbaseid=s4.specbaseid " & vbCrLf &
                                 "left join spec s5 on s5.specid=t.fiveweekspecid " & vbCrLf &
                                 "left join specbase sb5 on sb5.specbaseid=s5.specbaseid " & vbCrLf &
                                 "WHERE t.productname IS NOT NULL " & vbCrLf

        If para.Keys.Contains("ProductName") = True Then
            strQuery += " AND LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' " & vbCrLf
        End If

        If para.Keys.Contains("planMonth") = True Then
            strQuery += " AND PlanMonth='" & para("planMonth").ToString().Trim() & "'" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            result = dal.RunProcedureForPage(paraPage)
            Dim tmpdt As DataTable = result.DBTable.Copy
            tmpdt.Columns.Add("conNumber")
            tmpdt.Columns.Add("countNumber")

            Dim strTmp As String = ""
            If tmpdt.Rows.Count > 0 Then
                For Each row As DataRow In tmpdt.Rows
                    strTmp = strTmp + "'" + row("mfgplanformonthname").ToString().Trim() + "',"
                Next
                strTmp = strTmp.Substring(0, strTmp.Length - 1)

                strQuery = String.Format("SELECT c.containername,c.qty,u.parentid,u.attributevalue FROM Userattribute u " & vbCrLf &
                           "LEFT JOIN Container c ON c.containerid=u.parentid " & vbCrLf &
                           "WHERE u.userattributename='monthPlanName' AND u.attributevalue IN ({0})", strTmp)
                Dim tmpTable As DataTable = dal.StandardGetDataTable(strQuery)

                For Each row As DataRow In tmpdt.Rows
                    Dim strFilter As String = String.Format("attributevalue='{0}'", row("mfgplanformonthname").ToString().Trim())
                    Dim tmpRows As DataRow() = tmpTable.Select(strFilter)
                    Dim intQty As Integer = 0
                    For Each row1 As DataRow In tmpRows
                        intQty = intQty + Convert.ToInt32(row1("qty"))
                    Next
                    row("conNumber") = tmpRows.Length
                    row("countNumber") = intQty
                Next
            End If

            tmpdt.Columns.Add("disponespecname")
            tmpdt.Columns.Add("disptwospecname")
            tmpdt.Columns.Add("disptreespecname")
            tmpdt.Columns.Add("dispfourspecname")
            tmpdt.Columns.Add("dispfivespecname")

            For Each row As DataRow In tmpdt.Rows
                row("disponespecname") = CurrentInfo.ReplaceProductNameForSpec(row("onespecname").ToString().Trim())
                row("disptwospecname") = CurrentInfo.ReplaceProductNameForSpec(row("twospecname").ToString().Trim())
                row("disptreespecname") = CurrentInfo.ReplaceProductNameForSpec(row("treespecname").ToString().Trim())
                row("dispfourspecname") = CurrentInfo.ReplaceProductNameForSpec(row("fourspecname").ToString().Trim())
                row("dispfivespecname") = CurrentInfo.ReplaceProductNameForSpec(row("fivespecname").ToString().Trim())
            Next
            result.Result = True
            result.DBTable = tmpdt.Copy()
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Function GetMfgOrderListForERP(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetMfgOrderListForERP

        Dim strQuery As String = "select t.mfgordername,t.mfgorderid,t.qty,pb.productname,p.productrevision,nvl(wb.workflowname,wb1.workflowname) workflowname ," & vbCrLf &
                                 "nvl(w.workflowrevision ,w1.workflowrevision) workflowrevisoin ,nvl(c.qty,0) conNumber,t.plannedstartdate,t.plannedcompletiondate   from MfgOrder t" & vbCrLf &
                                 "left join productbase pb1 on pb1.productbaseid=t.productbaseid " & vbCrLf &
                                 "left join product p1 on p1.productid=t.productid" & vbCrLf &
                                 "left join product p on p.productid=nvl(pb1.revofrcdid,t.productid) " & vbCrLf &
                                 "left join productbase pb on pb.productbaseid=nvl(p1.productbaseid,t.productbaseid) " & vbCrLf &
                                 "left join workflow w on w.workflowid=p.workflowid " & vbCrLf &
                                 "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf &
                                 "left join workflowbase wb1 on wb1.workflowbaseid=p.workflowbaseid  " & vbCrLf &
                                 "left join workflow w1 on w1.workflowid=wb1.revofrcdid " & vbCrLf &
                                 "left join(select c.mfgorderid,sum(c.qty) qty from container c " & vbCrLf &
                                 " left join product p on p.productid=c.productid  " & vbCrLf &
                                 " left join productbase pb on pb.productbaseid = p.productbaseid" & vbCrLf &
                                 "where c.mfgorderid is not null and pb.productname not like '%(H)' group by c.mfgorderid) c " & vbCrLf &
                                 "on c.mfgorderid = t.mfgorderid " & vbCrLf &
                                 " 	LEFT JOIN cdodefinition cdo ON cdo.cdodefid=t.cdotypeid	" & vbCrLf &
                                 "WHERE t.qty>0 and cdo.cdoname='MfgOrder'" & vbCrLf
        'add:Wangjh 2017-7-21 增加cdoname判断
        If para.Keys.Contains("ProductName") Then
            strQuery += " AND LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' " & vbCrLf
        End If

        If para.Keys.Contains("OrderStatus") Then
            If para("OrderStatus").ToString = "1" Then
                strQuery += " AND nvl(c.qty,0)<t.qty" & vbCrLf
            Else
                strQuery += " AND nvl(c.qty,0)>=t.qty" & vbCrLf
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            result = dal.RunProcedureForPage(paraPage)
            'tmpSet.Tables(0).Columns.Add("conNumber")
            'tmpSet.Tables(0).Columns.Add("countNumber")

            'Dim strTmp As String = ""
            'If tmpSet.Tables(0).Rows.Count > 0 Then
            '    strQuery = String.Format("select c.mfgorderid,sum(c.qty) qty from container c where c.mfgorderid is not null group by c.mfgorderid")
            '    Dim tmpTable As DataTable = dal.StandardGetDataTable(strQuery)

            '    For Each row As DataRow In tmpSet.Tables(0).Rows
            '        Dim strFilter As String = String.Format("mfgorderid='{0}'", row("mfgorderid").ToString().Trim())
            '        Dim tmpRows As DataRow() = tmpTable.Select(strFilter)
            '        Dim intQty As Integer = 0
            '        If tmpRows.Length > 0 Then
            '            intQty = tmpRows(0)("qty")
            '        End If
            '        row("conNumber") = intQty
            '    Next
            'End If
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

#End Region

#Region "获取ERP生产任务列表"
    Function GetInventTaskList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetInventTaskList
        Dim strQuery As String = "SELECT it.createmodifieddate,it.departmentid,it.inventsizeid,it.inventtaskinfoid,it.inventtaskinfoname,it.itemid,it.itemname,it.publishtime," & vbCrLf & _
                                "it.journalstatus,it.planbegindate,it.planenddate,it.qty,it.taskcontent,it.taskstatus,it.tasktype,it.unitid,it.urgent,it.iscreatemfgorder,it.description " & vbCrLf & _
                                "FROM inventtaskinfo it" & vbCrLf & _
                                "WHERE 1 = 1 AND it.isread = 0" & vbCrLf

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND it.departmentid = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(it.itemid) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(it.itemname) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("MfgTaskNo") = True Then
            strQuery += "AND (LOWER(it.inventtaskinfoname) LIKE '%" & para("MfgTaskNo").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND it.createmodifieddate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND it.createmodifieddate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try

            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取质量记载单列表"
    Function GetQualityRecordList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetQualityRecordList
        Dim strQuery As String = "SELECT c.containername,pb.productname,p.description,p.productrevision,sb.specname,s.specrevision,qr.qty,pf.productfamilyname,pm.phasemarkname," & vbCrLf & _
                                "   e.employeename AS checkemployeename,e.fullname AS checkfullname,qr.checkdate,qr.recordstatus,qr.qualityrecordname," & vbCrLf & _
                                "   (CASE WHEN qr.recordstatus = 0 THEN '工人提交' WHEN qr.recordstatus = 1 THEN '检验提交' WHEN qr.recordstatus = 2 THEN '工艺已确认' WHEN qr.recordstatus = 5 THEN '工艺已退回' END) AS status," & vbCrLf & _
                                "   (CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "   (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "   e1.employeename AS toployeename,e1.fullname AS tofullname" & vbCrLf & _
                                "FROM qualityrecord qr" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = qr.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = qr.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = qr.checkemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e1 ON e1.employeeid = qr.technologistemployeeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE qr.recordstatus IN (" & vbCrLf

        If para.Keys.Contains("IsWorkerQRToTech") = True Then
            strQuery += "0,"
        End If
        strQuery += "1,2)" & vbCrLf


        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        'If para.Keys.Contains("SecretLevel") = True Then
        '    strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        'End If
        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND wc.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("SpecName") = True Then
            strQuery += "AND (LOWER(sb.specname) LIKE '%" & para("MfgTaskNo").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("specno") = True Then
            strQuery += "AND (LOWER(s.specno) = '" & para("specno").ToString.ToLower & "')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND qr.checkdate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND qr.checkdate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try

            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取质量记载单的零件编号列表"
    Function GetProductNoListByQualityRecord(ByVal strName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoListByQualityRecord
        Dim strQuery As String = "SELECT q.qualityrecordname,pi.productno,pi.qualityrecordnotes,qd.qualitydisposeadvicename,pi.qualitydisposenotes," & vbCrLf & _
                                "pi.containerlist AS speclist,pi.qty,replace(pi.productno,c.containername || '-') AS productnodisplay" & vbCrLf & _
                                "FROM productnoinfo pi" & vbCrLf & _
                                "LEFT JOIN qualityrecordproductnoinfo qi ON qi.productnoinfoid = pi.productnoinfoid" & vbCrLf & _
                                "LEFT JOIN qualityrecord q ON q.qualityrecordid = qi.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN qualitydisposeadvice qd ON qd.qualitydisposeadviceid = pi.qualitydisposeadviceid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = q.containerid" & vbCrLf & _
                                "WHERE q.qualityrecordname = '" & strName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoListByQualityRecord(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡基本信息"
    Function GetContainerInfo(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetContainerInfo

        Dim strSql As String = "SELECT c.containername,c.togetherflag,pb.productname,cu.specid,cu.workflowstepid FROM container c " & vbCrLf & _
                               "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid " & vbCrLf & _
                               "WHERE c.containername='" & strContainerName & "'"

        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.StandardGetDataTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡对应的生产订单工艺确认信息"
    Function GetContainerTechConfirmInfo(ByVal strContainerID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetContainerTechConfirmInfo
        Dim strQuery As String = "SELECT c.containerid,c.containername,mo.mfgordername,mo.techconfirm,mo.techconfirmdate," & vbCrLf & _
                                "   e.employeeid,e.employeename,e.fullname" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = mo.techconfirmemployeeid" & vbCrLf & _
                                "WHERE c.containerid = '" & strContainerID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerTechConfirmInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工序信息"
    Function GetSpecInfo(ByVal strSpecID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetSpecInfo
        Dim strQuery As String = "SELECT sb.specname,s.specrevision,s.specid,o.operationname" & vbCrLf & _
                                "FROM spec s" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN operation o ON o.operationid = s.operationid" & vbCrLf & _
                                "WHERE s.specid = '" & strSpecID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetSpecInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取已经通过 工段长接收 节点的批次"
    Function GetContainerHasMaterial(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetContainerHasMaterial
        Dim strQuery As String = "SELECT distinct mo.mfgordername,c.containername,pf.productfamilyname,pm.phasemarkname,pb.productname,p.description," & vbCrLf & _
                                "   c.originalqty,c.qty,pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate,sb.specname,c.containerid,p.productid" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = c.originalfactoryid" & vbCrLf & _
                                "LEFT JOIN (" & vbCrLf & _
                                "   SELECT hml.containerid" & vbCrLf & _
                                "   FROM movehistory mh" & vbCrLf & _
                                "   LEFT JOIN historymainline hml ON hml.historymainlineid = mh.historymainlineid" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = hml.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN cdodefinition cdo ON cdo.cdodefid = hml.txntype" & vbCrLf & _
                                "   WHERE hml.reversalstatus = 1 AND cdo.cdoname = 'MoveStd'" & vbCrLf & _
                                "   AND (wfs.workflowstepname = '工段长接收' or wfs.workflowstepname = '领料')" & vbCrLf & _
                                ") h ON h.containerid = c.containerid" & vbCrLf & _
                                "WHERE c.parentcontainerid IS NULL AND c.containerid <> c.containername" & vbCrLf & _
                                "AND h.containerid IS NOT NULL" & vbCrLf

        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername ASC" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡未开合格证的产品序号"
    Function GetProductNoByContainerName(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetProductNoByContainerName
        Dim strQuery As String = "SELECT REPLACE(c.containername,'" & strContainerName & "' || '-','') AS ContainerName" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN container cc ON cc.containerid = c.parentcontainerid" & vbCrLf & _
                                "WHERE cc.containername = '" & strContainerName & "'AND c.status = 1 AND c.qty > 0" & vbCrLf & _
                                "AND" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT q.qualificationname FROM qualification q" & vbCrLf & _
                                "   WHERE ',' || q.productnoinfo || ',' LIKE '%,' || REPLACE(c.containername,'" & strContainerName & "' || '-','') || ',%'" & vbCrLf & _
                                ") IS NULL" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByContainerName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡基本信息"
    Function GetContainerInfoByID(ByVal strContainerID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetContainerInfoByID
        Dim strQuery As String = "SELECT c.containername,pb.productname,p.description,c.originalqty,c.qty,c.containerid," & vbCrLf & _
                                "   sb.specname,c.plannedstartdate,c.plannedcompletiondate,cpn.containerproductnoname," & vbCrLf & _
                                "   wfs.workflowstepname,wf.workflowid,wf.workflowrevision,wfb.workflowname,cdo.cdoname" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN currentstatus cs ON cs.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cs.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cs.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN containerproductno cpn ON cpn.containerproductnoname = c.containername" & vbCrLf & _
                                "WHERE c.containerid = '" & strContainerID & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取批次当前工序以后的工序列表"
    Function GetLeftSpecListByContainer(ByVal strContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetLeftSpecListByContainer
        Dim strQuery As String = "SELECT wstep.*,sb.specname,s.specrevision" & vbCrLf & _
                                "FROM spec s" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "RIGHT JOIN" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT wfs.workflowstepname,wfs.specbaseid,wfs.specid AS specid1,wfs.sequence," & vbCrLf & _
                                "       (CASE WHEN wfs.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = wfs.specbaseid) ELSE wfs.specid END) AS specid" & vbCrLf & _
                                "   FROM workflowstep wfs" & vbCrLf & _
                                "   RIGHT JOIN" & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT wfs1.sequence,wfs1.workflowid FROM workflowstep wfs1" & vbCrLf & _
                                "       LEFT JOIN currentstatus cu ON cu.workflowstepid = wfs1.workflowstepid" & vbCrLf & _
                                "       LEFT JOIN container c ON c.currentstatusid = cu.currentstatusid" & vbCrLf & _
                                "       WHERE c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "   ) wf ON wfs.workflowid = wf.workflowid" & vbCrLf & _
                                "   WHERE wfs.sequence > wf.sequence" & vbCrLf & _
                                ") wstep ON wstep.specid = s.specid" & vbCrLf & _
                                "ORDER BY wstep.sequence"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetLeftSpecListByContainer(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "创建批次"
    Function StartContainer(ByVal para As ContainerStartInfo) As uMESProcessResult Implements uMESDispatchIBusiness.StartContainer
        Dim result As New uMESProcessResult
        Try
            Dim strWorkflowName As String = para.WorkflowName
            Dim strContainerName As String = para.ContainerName
            Dim boolUseROR As String = para.WorkflowuseROR

            '创建批次
            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ContainerName"
            dataEntity.ClientDataValue = para.ContainerName
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Level"
            dataEntity.ClientDataValue = para.Level
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            Dim strProdName As String = para.ProductName
            Dim strProdRev As String = para.ProductRevision

            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Product"
            dataEntity.ClientDataValue = strProdName
            dataEntity.ClientDataVersion = strProdRev
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Owner"
            dataEntity.ClientDataValue = para.Owner
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Qty"
            dataEntity.ClientDataValue = para.Qty
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "IsSelfMfg"
            dataEntity.ClientDataValue = para.IsSelfMfg
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "UOM"
            dataEntity.ClientDataValue = para.UOM
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "StartReason"
            dataEntity.ClientDataValue = para.StartReason
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.CurrentStatusDetails
            dataEntity.ClientDataName = "Workflow"
            dataEntity.ClientDataValue = para.WorkflowName
            dataEntity.ClientDataVersion = para.WorkflowRevision

            'If para.IsNewWorkflowRev = True Then
            '    dataEntity.ClientDataVersion = para.ContainerName
            'Else
            '    dataEntity.ClientDataVersion = para.WorkflowRevision
            'End If

            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.CurrentStatusDetails
            dataEntity.ClientDataName = "Factory"
            dataEntity.ClientDataValue = para.Factory
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.subentityField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.CurrentStatusDetails
            dataEntity.ClientDataName = "Location"
            dataEntity.ClientDataValue = para.Location
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "MfgOrder"
            dataEntity.ClientDataValue = para.MfgOrder
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "WorkCenter"
            dataEntity.ClientDataValue = para.WorkCenter
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "PriorityCode"
            dataEntity.ClientDataValue = para.Priority
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "PlannedStartDate"
            dataEntity.ClientDataValue = para.PlannedStartDate
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "PlannedCompletionDate"
            dataEntity.ClientDataValue = para.PlannedCompletionDate
            m_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "MaterialRequirementDate"
            'dataEntity.ClientDataValue = para.MaterialRequirementDate
            'm_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "StartEmployee"
            dataEntity.ClientDataValue = para.StartEmployee
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "QualificationNum"
            dataEntity.ClientDataValue = para.ContainerNo
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ContainerType"
            dataEntity.ClientDataValue = para.ContainerType
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "TogetherFlag"
            dataEntity.ClientDataValue = para.TogetherFlag
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ContainerComment"
            dataEntity.ClientDataValue = para.Comment
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "MfgBatch"
            dataEntity.ClientDataValue = para.MfgBatch
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "MfgWorkflow"
            dataEntity.ClientDataValue = para.MfgWorkflowName
            dataEntity.ClientDataVersion = para.MfgWorkflowRevision
            m_DataList.Add(dataEntity)

            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim dtChildList As DataTable = para.ChildList

            If dtChildList IsNot Nothing Then
                If dtChildList.Rows.Count > 0 Then
                    For i As Integer = 0 To dtChildList.Rows.Count - 1
                        Dim m_Child As New List(Of ClientAPIEntity)
                        Dim stepEntity As New ClientAPIEntity

                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "ContainerName"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("ContainerName").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                        stepEntity.ClientDataName = "Level"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("Level").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "Qty"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("Qty").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "ContainerType"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("ContainerType").ToString
                        m_Child.Add(stepEntity)

                        m_ChildList.Add(i.ToString, m_Child)
                    Next
                End If
            End If

            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = para.ServerHostInfo
            Dim serverHost As String = daServerHost.ServerHost
            Dim serverPort As String = daServerHost.ServerPort
            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            '创建批次结束
            Try
                Dim api As New CamstarClientAPI(serverUser, serverPassword)
                result.Result = api.Start(m_DataList, m_ChildList, strInfo)
                result.Message = strInfo
            Catch ex As Exception
                result.Result = False
                result.Message = ex.Message
            End Try

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "批次属性登记"
    Public Function SaveDenamicAttribute(ByVal paraList As List(Of ContainerStartInfo)) As uMESProcessResult Implements uMESDispatchIBusiness.SaveDenamicAttribute

        Dim result As New uMESProcessResult()
        Dim strInfo As String = ""

        Dim m_AttrList As New Dictionary(Of String, List(Of ClientAPIEntity))

        For i As Integer = 0 To paraList.Count - 1
            Dim m_AttrChild As New List(Of ClientAPIEntity)

            Dim attEntity As New ClientAPIEntity
            attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            attEntity.ClientInputTypeEnum = InputTypeEnum.Details
            attEntity.ClientDataName = "Name"
            attEntity.ClientDataValue = paraList(i).AttributeName
            m_AttrChild.Add(attEntity)

            attEntity = New ClientAPIEntity()
            attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            attEntity.ClientInputTypeEnum = InputTypeEnum.Details
            attEntity.ClientDataName = "DataType"
            attEntity.ClientDataValue = "4"
            m_AttrChild.Add(attEntity)

            attEntity = New ClientAPIEntity()
            attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            attEntity.ClientInputTypeEnum = InputTypeEnum.Details
            attEntity.ClientDataName = "AttributeValue"
            attEntity.ClientDataValue = paraList(i).attributeValue
            m_AttrChild.Add(attEntity)

            m_AttrList.Add(i.ToString().Trim(), m_AttrChild)
        Next

        Dim m_DataList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Container"
        dataEntity.ClientDataValue = paraList(0).ContainerName
        dataEntity.ClientOtherValue = paraList(0).Level
        m_DataList.Add(dataEntity)
        Dim daServerHost As ServerHostInfo = paraList(0).ServerHostInfo
        Dim serverHost As String = daServerHost.ServerHost
        Dim serverPort As String = daServerHost.ServerPort
        Dim serverUser As String = daServerHost.UserName
        Dim serverPassword As String = daServerHost.Password

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim success As Boolean = api.RunTxnServiceHasSubentity("ContainerAttrMaintDoc", "ContainerAttrMaint", m_DataList, "ServiceDetails", m_AttrList, strInfo)
        result.Result = success
        result.Message = strInfo
        Return result
    End Function

    Public Function SaveMageSysAttribute(ByVal strContainerName As String, ByVal strValue As String, ByVal strUserName As String, ByVal strPassWord As String) As String

        Dim result As New uMESProcessResult()
        Dim strInfo As String = ""

        Dim m_AttrList As New Dictionary(Of String, List(Of ClientAPIEntity))

        Dim m_AttrChild As New List(Of ClientAPIEntity)

        Dim attEntity As New ClientAPIEntity
        attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        attEntity.ClientInputTypeEnum = InputTypeEnum.Details
        attEntity.ClientDataName = "Name"
        attEntity.ClientDataValue = "currSpecStatus"
        m_AttrChild.Add(attEntity)

        attEntity = New ClientAPIEntity()
        attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        attEntity.ClientInputTypeEnum = InputTypeEnum.Details
        attEntity.ClientDataName = "DataType"
        attEntity.ClientDataValue = "4"
        m_AttrChild.Add(attEntity)

        attEntity = New ClientAPIEntity()
        attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        attEntity.ClientInputTypeEnum = InputTypeEnum.Details
        attEntity.ClientDataName = "AttributeValue"
        attEntity.ClientDataValue = strValue
        m_AttrChild.Add(attEntity)

        m_AttrList.Add("0", m_AttrChild)


        Dim m_DataList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Container"
        dataEntity.ClientDataValue = strContainerName
        dataEntity.ClientOtherValue = "Lot"
        m_DataList.Add(dataEntity)

        Dim serverUser As String = strUserName
        Dim serverPassword As String = strPassWord

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim success As Boolean = api.RunTxnServiceHasSubentity("ContainerAttrMaintDoc", "ContainerAttrMaint", m_DataList, "ServiceDetails", m_AttrList, strInfo)
        If success = False Then
            Return strInfo
        Else
            Return ""
        End If
    End Function

#End Region

#Region "Split"
    Function SplitContainer(ByVal para As ContainerSplitInfo) As uMESProcessResult Implements uMESDispatchIBusiness.SplitContainer
        Dim result As New uMESProcessResult
        Try
            Dim strContainerName As String = para.ContainerName

            '拆分批次
            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = strContainerName
            dataEntity.ClientOtherValue = "Lot"
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            m_DataList.Add(dataEntity)

            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim ToInfoList As List(Of ToContainerInfo) = para.ToContainerInfo

            Dim m_Child As New List(Of ClientAPIEntity)
            For i As Integer = 0 To ToInfoList.Count - 1
                Dim childEntity As New ClientAPIEntity
                childEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                childEntity.ClientDataName = "ToContainerName"
                childEntity.ClientDataValue = ToInfoList(i).ToContainerName
                m_Child.Add(childEntity)

                childEntity = New ClientAPIEntity
                childEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                childEntity.ClientDataName = "Qty"
                childEntity.ClientDataValue = ToInfoList(i).Qty
                m_Child.Add(childEntity)

                m_ChildList.Add(i.ToString, m_Child)
            Next

            Dim dtChildList As DataTable = para.ChildContainer

            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = para.ServerHostInfo
            Dim serverHost As String = daServerHost.ServerHost
            Dim serverPort As String = daServerHost.ServerPort
            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Try
                Dim api As New CamstarClientAPI(serverUser, serverPassword)
                result.Result = api.Split(m_DataList, m_ChildList, dtChildList, strInfo)
                result.Message = strInfo
            Catch ex As Exception
                result.Result = False
                result.Message = ex.Message
            End Try

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "工艺确认生产订单"
    Function TechConfirmMfgOrder(ByVal para As MfgOrderInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMESDispatchIBusiness.TechConfirmMfgOrder
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "TechConfirm"
        dataEntity.ClientDataValue = para.TechConfirm
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "TechConfirmDate"
        dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "TechConfirmEmployee"
        dataEntity.ClientDataValue = para.TechConfirmEmployee
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Notes"
        dataEntity.ClientDataValue = para.Notes
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateModelByStandard("MfgOrderMaintDoc", "MfgOrderMaint", para.Name, "", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "创建生产订单"
    Function CreateMfgOrder(ByVal para As MfgOrderInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMESDispatchIBusiness.CreateMfgOrder
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para.Name
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgTaskNo"
        dataEntity.ClientDataValue = para.MfgTaskNo
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientDataName = "Product"
        dataEntity.ClientDataValue = para.ProductName
        dataEntity.ClientDataVersion = para.ProductRevision
        dataEntity.ClientDataIsROR = para.ProductuseROR
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para.Qty
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "UOM"
        dataEntity.ClientDataValue = para.UOM
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "Priority"
        dataEntity.ClientDataValue = para.Priority
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "ReportingFactory"
        dataEntity.ClientDataValue = para.Factory
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para.PlannedStartDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para.PlannedCompletionDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgBatch"
        dataEntity.ClientDataValue = para.MfgBatch
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "InventTaskInfo"
        dataEntity.ClientDataValue = para.InventTaskInfoName
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.CreateModelByStandard("MfgOrderMaintDoc", "MfgOrderMaint", "New", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "修改生产订单"
    Function UpdateMfgOrder(ByVal para As MfgOrderInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateMfgOrder
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgTaskNo"
        dataEntity.ClientDataValue = para.MfgTaskNo
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientDataName = "Product"
        dataEntity.ClientDataValue = para.ProductName
        dataEntity.ClientDataVersion = para.ProductRevision
        dataEntity.ClientDataIsROR = para.ProductuseROR
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para.Qty
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "UOM"
        dataEntity.ClientDataValue = para.UOM
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "Priority"
        dataEntity.ClientDataValue = para.Priority
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "ReportingFactory"
        dataEntity.ClientDataValue = para.Factory
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para.PlannedStartDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para.PlannedCompletionDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgBatch"
        dataEntity.ClientDataValue = para.MfgBatch
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "InventTaskInfo"
        dataEntity.ClientDataValue = para.InventTaskInfoName
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateModelByStandard("MfgOrderMaintDoc", "MfgOrderMaint", para.Name, "", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "修改生产任务"
    Function UpdateInventTaskInfo(ByVal para As InventTaskInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateInventTaskInfo
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "IsCreateMfgOrder"
        dataEntity.ClientDataValue = para.IsCreateMfgOrder
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "IsRead"
        dataEntity.ClientDataValue = para.IsRead
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateModelByStandard("InventTaskInfoMaintDoc", "InventTaskInfoMaint", para.Name, "", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "添加或更新批次零件编号信息"
    Function AddAndUpdateContainerProductNo(ByVal p_UpdateName As String, ByVal p_perform As String, ByVal p_dataEntityList As List(Of ClientAPIEntity), _
                                      ByVal p_dataProductList As Dictionary(Of String, List(Of ClientAPIEntity))) As uMESProcessResult Implements uMESDispatchIBusiness.AddAndUpdateContainerProductNo
        Dim result As New uMESProcessResult
        result.Result = False
        Return result
    End Function
#End Region

#Region "检查报工单的报工情况"
    Public Function GetWorkCenterReportInfo(ByVal workCenterDispatchingName As String) As DataTable Implements uMESDispatchIBusiness.GetWorkCenterReportInfo
        Dim strSql As String = "SELECT * FROM workcenterdispatching t WHERE t.workcenterdispatchingname='" & workCenterDispatchingName & "'"
        Dim tmpTable As DataTable = dal.StandardGetDataTable(strSql)
        Return tmpTable
    End Function
#End Region

#Region "系统管理"
#Region "业务数据整理"
    Public Function ManageSys(ByVal serverInfo As ServerHostInfo) As String Implements uMESDispatchIBusiness.ManageSys

        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

        'Dim strQuery As String = "select *  from conDispatch t where t.containername='A1517S10.12.9201/1608001' "

        Dim strQuery As String = "SELECT c.containerid,c.containername,wp.workflowstepid,wp.workflowstepname,wp.specid,sb.specname,sb1.revofrcdid,sb1.specname   FROM container c " & vbCrLf & _
                                  "LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid " & vbCrLf & _
                                  "LEFT JOIN Workflowstep wp ON wp.workflowstepid=cu.workflowstepid " & vbCrLf & _
                                  "LEFT JOIN  spec s ON s.specid=wp.specid " & vbCrLf & _
                                  "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                                  "LEFT JOIN specbase sb1 ON sb1.specbaseid=wp.specbaseid " & vbCrLf & _
                                  "WHERE c.parentcontainerid IS NULL AND c.containerid<>c.containername AND c.status=1 "
        'AND c.containername='A1517S10.12.9201/1608001'

        Dim strMessage As String = ""

        Dim tmpTable As DataTable = dal.GetSpecDispatchInfo(strQuery)

        For Each row As DataRow In tmpTable.Rows
            Dim strSpecID As String = ""
            If row("specid").ToString().Trim() = "0000000000000000" Then
                strSpecID = row("revofrcdid").ToString().Trim()
            Else
                strSpecID = row("specid").ToString().Trim()
            End If

            'Dim strSql As String = String.Format("SELECT c.containerid,c.containername,t.userattributeid,t.attributevalue FROM userattribute  t " & vbCrLf & _
            '                "LEFT JOIN CONTAINER c ON c.containerid=t.parentid " & vbCrLf & _
            '                "WHERE t.userattributename='currSpecStatus'AND c.containerid='{0}'", row("containerid").ToString().Trim())

            'Dim DT As DataTable = dal.StandardGetDataTable(strSql)
            'If DT.Rows.Count > 0 Then
            '    Continue For
            'End If

            '得到转工信息
            strQuery = "SELECT sy.synergicspecvalidatename,sy.containerid ,sy1.specid FROM synergicspecvalidate sy " & vbCrLf
            strQuery += "INNER JOIN synergicspecinfo sy1 ON sy1.synergicspecvalidateid=sy.synergicspecvalidateid " & vbCrLf
            strQuery += " WHERE sy.containerid = '" + row("containerid").ToString().Trim() + "' AND sy1.specid = '" + strSpecID + "'"
            Dim syncTable As DataTable = dal.GetSpecDispatchInfo(strQuery)

            If syncTable.Rows.Count > 0 Then '登记已转工
                Dim tmp1Enity As ContainerStartInfo = New ContainerStartInfo()
                tmp1Enity.AttributeName = "currSpecStatus"
                tmp1Enity.attributeValue = "4"
                tmp1Enity.ContainerName = row("containername").ToString().Trim()
                tmp1Enity.Level = ""
                tmp1Enity.ServerHostInfo = serverInfo


                Dim result1 As uMESProcessResult = commBusiness.SetUserAttribute(tmp1Enity)
                If result1.Message <> "" Then
                    strMessage = strMessage + " " + result1.Message
                End If
                Continue For
            End If

            '查找派工信息
            strQuery = "SELECT t.containerid,t.specid,nvl(t.qty,0) as qty ,nvl( t.reportedqty,0) as reportedqty,t.mainmfgemployeeid,e.fullname  FROM WORKCENTERDISPATCHING t " & vbCrLf
            strQuery += " LEFT JOIN employee e ON e.employeeid=t.mainmfgemployeeid " & vbCrLf
            strQuery += " WHERE  t.containerid = '" + row("containerid").ToString().Trim() + "'  AND t.specid = '" + strSpecID + "'"
            Dim dispatchTable As DataTable = dal.GetSpecDispatchInfo(strQuery)

            If dispatchTable.Rows.Count = 0 Then '登记待派工

                Dim tmp1Enity As ContainerStartInfo = New ContainerStartInfo()
                tmp1Enity.AttributeName = "currSpecStatus"
                tmp1Enity.attributeValue = "1"
                tmp1Enity.ContainerName = row("containername").ToString().Trim()
                tmp1Enity.Level = ""
                tmp1Enity.ServerHostInfo = serverInfo


                Dim result1 As uMESProcessResult = commBusiness.SetUserAttribute(tmp1Enity)
                If result1.Message <> "" Then
                    strMessage = strMessage + " " + result1.Message
                End If
                Continue For
            Else
                Dim dqty As Integer = 0
                Dim reportQty As Integer = 0
                '计算派工和报工数据
                For Each dispatchRow As DataRow In dispatchTable.Rows
                    dqty = dqty + Convert.ToInt32(dispatchRow("qty"))
                    reportQty = reportQty + Convert.ToInt32(dispatchRow("reportedqty"))
                Next
                If dqty > 0 And reportQty = 0 Then
                    'Dim strInfo As String = SaveMageSysAttribute(row("containername").ToString().Trim(), 2, serverInfo.UserName, serverInfo.Password) '待报工
                    'If strInfo.Trim() <> "" Then
                    '    strMessage = strMessage + " " + strInfo
                    'End If
                    Dim tmp1Enity As ContainerStartInfo = New ContainerStartInfo()
                    tmp1Enity.AttributeName = "currSpecStatus"
                    tmp1Enity.attributeValue = "2"
                    tmp1Enity.ContainerName = row("containername").ToString().Trim()
                    tmp1Enity.Level = ""
                    tmp1Enity.ServerHostInfo = serverInfo

                    Dim result1 As uMESProcessResult = commBusiness.SetUserAttribute(tmp1Enity)
                    If result1.Message <> "" Then
                        strMessage = strMessage + " " + result1.Message
                    End If

                Else
                    '查找质量信息
                    strQuery = "select nvl(convh.eligibilityqty,0) AS eligibilityqty ,NVL(convh.nonsenseqty,0) as nonsenseqty,h.containerid,h.workflowstepid  from  conventioncheckhistory convh " & vbCrLf
                    strQuery += "inner join historymainline h on h.historymainlineid=convh.historymainlineid "
                    strQuery += "WHERE  h.containerid = '" + row("containername").ToString().Trim() + "' AND h.workflowstepid = '" + strSpecID + "'"
                    Dim checkTable As DataTable = dal.GetSpecDispatchInfo(strQuery)
                    If checkTable.Rows.Count = 0 Then
                        'Dim strInfo As String = SaveMageSysAttribute(row("containername").ToString().Trim(), 3, serverInfo.UserName, serverInfo.Password) '待检验
                        'If strInfo.Trim() <> "" Then
                        '    strMessage = strMessage + " " + strInfo
                        'End If
                        Dim tmp1Enity As ContainerStartInfo = New ContainerStartInfo()
                        tmp1Enity.AttributeName = "currSpecStatus"
                        tmp1Enity.attributeValue = "3"
                        tmp1Enity.ContainerName = row("containername").ToString().Trim()
                        tmp1Enity.Level = ""
                        tmp1Enity.ServerHostInfo = serverInfo

                        Dim result1 As uMESProcessResult = commBusiness.SetUserAttribute(tmp1Enity)
                        If result1.Message <> "" Then
                            strMessage = strMessage + " " + result1.Message
                        End If

                    Else
                        'Dim strInfo As String = SaveMageSysAttribute(row("containername").ToString().Trim(), 1, serverInfo.UserName, serverInfo.Password) '待派工
                        'If strInfo.Trim() <> "" Then
                        '    strMessage = strMessage + " " + strInfo
                        'End If
                        Dim tmp1Enity As ContainerStartInfo = New ContainerStartInfo()
                        tmp1Enity.AttributeName = "currSpecStatus"
                        tmp1Enity.attributeValue = "1"
                        tmp1Enity.ContainerName = row("containername").ToString().Trim()
                        tmp1Enity.Level = ""
                        tmp1Enity.ServerHostInfo = serverInfo

                        Dim result1 As uMESProcessResult = commBusiness.SetUserAttribute(tmp1Enity)
                        If result1.Message <> "" Then
                            strMessage = strMessage + " " + result1.Message
                        End If
                    End If
                End If
            End If
        Next
        Return strMessage
    End Function
#End Region

#Region "工艺员补录"
    Public Function MeangeTec(ByVal serverInfo As ServerHostInfo, ByVal strTecName As String) As String Implements uMESDispatchIBusiness.MeangeTec
        Dim sql As String = "SELECT DISTINCT c.containername,c.containerid,c.mfgworkflowid,pb.productname,p.productrevision,c.productid,p.description,f.factoryname,sb.specname," & vbCrLf & _
                            "s.specrevision, ws.workflowstepname,cts.specid,cts.workflowstepid,cl.containerlevelname," & vbCrLf & _
                            "c.qty,cts.lastmovedate,c.materialrequirementdate,c.plannedstartdate, " & vbCrLf & _
                            "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold,p.materialspecification,p.materialsize,p.MaterialTrademark,'' as iszhaohui,td1.CheckTechnique " & vbCrLf & _
                            "FROM container c " & vbCrLf & _
                            "LEFT JOIN containerlevel cl ON cl.containerlevelid = c.levelid " & vbCrLf & _
                            "LEFT JOIN product p ON p.productid = c.productid " & vbCrLf & _
                            "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid " & vbCrLf & _
                            "LEFT JOIN currentstatus cts ON cts.currentstatusid = c.currentstatusid " & vbCrLf & _
                            "LEFT JOIN spec s ON s.specid = cts.specid " & vbCrLf & _
                            "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid " & vbCrLf & _
                            "LEFT JOIN workflowstep ws ON ws.workflowstepid = cts.workflowstepid " & vbCrLf & _
                            "LEFT JOIN workflow w on w.workflowid=ws.workflowid " & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo on cdo.cdodefid=w.cdotypeid " & vbCrLf & _
                            "LEFT JOIN factory f ON f.factoryid = cts.factoryid " & vbCrLf & _
                            "LEFT JOIN ( " & vbCrLf & _
                            "select td1.parentid as containerid, " & vbCrLf & _
                            "(select td.attributevalue from userattribute td where td.userattributename = 'CheckTechnique' and td.parentid = td1.parentid) as CheckTechnique " & vbCrLf & _
                            "from userattribute td1 " & vbCrLf & _
                            ") td1 ON td1.containerid=c.containerid " & vbCrLf & _
                            "WHERE c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL " & vbCrLf & _
                            "AND ws.workflowstepname IS NOT NULL and cdo.cdoname='Workflow' AND  td1.CheckTechnique IS NULL"

        Dim containerTable As DataTable = dal.GetSpecDispatchInfo(sql)

        For Each row As DataRow In containerTable.Rows

            Dim attList As List(Of ContainerStartInfo) = New List(Of ContainerStartInfo)

            sql = String.Format("SELECT c.containerid,c.containername,t.userattributeid,t.attributevalue,t.userattributename FROM userattribute  t " & vbCrLf & _
                                   "LEFT JOIN CONTAINER c ON c.containerid=t.parentid " & vbCrLf & _
                                   "WHERE t.userattributename='currSpecStatus'AND c.containername='{0}'", row("containername").ToString().Trim())

            Dim DT As DataTable = dal.StandardGetDataTable(sql)

            For Each rowTmp As DataRow In DT.Rows
                Dim attPara As New ContainerStartInfo
                attPara.ServerHostInfo = serverInfo
                attPara.ContainerName = row("containername").ToString().Trim()
                attPara.Level = row("containerlevelname").ToString().Trim()
                attPara.AttributeName = rowTmp("userattributename").ToString().Trim()
                attPara.attributeValue = rowTmp("attributevalue").ToString().Trim()
                attList.Add(attPara)
            Next

            Dim attPara1 As New ContainerStartInfo
            attPara1.ServerHostInfo = serverInfo
            attPara1.ContainerName = row("containername").ToString().Trim()
            attPara1.Level = row("containerlevelname").ToString().Trim()
            attPara1.AttributeName = "CheckTechnique"
            attPara1.attributeValue = strTecName
            attList.Add(attPara1)
            SaveDenamicAttribute(attList)
        Next

        Return ""

    End Function
#End Region

#Region "机型数据迁移"
    Public Function ProductFamilyMove() As String Implements uMESDispatchIBusiness.ProductFamilyMove
        Dim strSql As String = " SELECT c.containerid,c.containername,c.containercomments,c.mfgbatch FROM CONTAINER c WHERE c.containercomments IS NOT NULL  AND  c.mfgbatch IS NULL "
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)

        For Each row As DataRow In DT.Rows
            If row("containercomments").ToString().Trim() = "" Then
                Continue For
            End If
            Dim strFamliy As String = "型别："
            Dim strMemo As String = ""

            Dim strTmp As String = row("containercomments").ToString().Trim()
            If row("containercomments").ToString().Trim().IndexOf("型别：") <> -1 Then '拆分型别文字
                Dim intLength As Integer = "型别：".Length
                strTmp = row("containercomments").ToString().Trim().Substring(intLength)
            End If
            strTmp = strTmp.Replace("|", " ")
            Dim strArray As String() = strTmp.Split(" ")
            Dim strTmpList As List(Of String) = New List(Of String)

            For i As Integer = 0 To strArray.Length - 1
                If strArray(i).Trim() = "" Then
                    Continue For
                End If
                strSql = String.Format("SELECT * FROM productfamily f WHERE f.productfamilyname='{0}'", strArray(i))
                Dim familyTable As DataTable = dal.StandardGetDataTable(strSql)
                If familyTable.Rows.Count = 0 Then
                    Continue For
                Else
                    strFamliy = strFamliy + strArray(i) + " "
                    strTmpList.Add(strArray(i))
                End If
            Next
            strFamliy = strFamliy + "|"
            For i As Integer = 0 To strTmpList.Count - 1
                strTmp = strTmp.Replace(strTmpList(i), " ")
            Next

            strSql = String.Format("UPDATE  CONTAINER c SET c.containercomments='{0}',c.mfgbatch='{1}' WHERE c.containername='{2}'", strTmp, strFamliy, row("containername").ToString().Trim())
            dal.ExecuteSql(strSql)
        Next

        strSql = " SELECT c.containerid,c.containername,c.containercomments,c.mfgbatch FROM CONTAINER c WHERE  c.mfgbatch IS NOT NULL "
        DT = dal.StandardGetDataTable(strSql)

        For Each row As DataRow In DT.Rows
            Dim strTmp As String = row("mfgbatch").ToString().Trim().Replace("型别：", " ")
            strTmp = strTmp.Replace("|", " ")
            Dim strArray As String() = strTmp.Split(" ")
            strTmp = "型别："
            For i As Integer = 0 To strArray.Length - 1
                If strArray(i).Trim() = "" Then
                    Continue For
                End If
                strTmp = strTmp + strArray(i) + " "
            Next
            strTmp = strTmp + "|"
            strSql = String.Format("UPDATE  CONTAINER c SET c.mfgbatch='{0}' WHERE c.containername='{1}'", strTmp, row("containername").ToString().Trim())
            dal.ExecuteSql(strSql)
        Next



        Return ""

    End Function

#End Region

#Region "大批次号整理"
    Public Sub SysContainerName() Implements uMESDispatchIBusiness.SysContainerName

        Dim strSql As String = "SELECT * FROM product"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)

        For Each row As DataRow In DT.Rows

            strSql = "SELECT * FROM container c WHERE c.parentcontainerid IS NULL AND c.status=1 AND c.containerid<>c.containername AND c.productid='" & row("productid").ToString().Trim() & "'"
            Dim conTable As DataTable = dal.StandardGetDataTable(strSql)

            For Each conRow As DataRow In conTable.Rows
                Dim strArray As String() = conRow("containername").ToString().Split("/")
                If strArray(1).Substring(0, 2) = "15" Then
                    Continue For
                End If

                Dim strF As String = strArray(1).Substring(0, 4)

                strSql = String.Format("UPDATE  CONTAINER c SET c.togetherflag='{0}' WHERE c.containername='{1}'", strF, conRow("containername").ToString().Trim())
                dal.ExecuteSql(strSql)
            Next

        Next
    End Sub

#End Region

#Region "物料批次号整理"
    Public Sub SysMContainer(ByVal serverInfo As ServerHostInfo) Implements uMESDispatchIBusiness.SysMContainer

        Dim CommonObject As uMESCommonBusiness = New uMESCommonBusiness()

        Dim strSql As String = "SELECT wt.workflowstepname,wt1.workflowstepname,h.containerid,c.containername,u.userattributename,  u.attributevalue,t.* FROM MOVEHISTORY t " & vbCrLf & _
                               "LEFT JOIN workflowstep wt ON wt.workflowstepid=t.stepid " & vbCrLf & _
                               "LEFT JOIN workflowstep wt1 ON wt1.workflowstepid=t.tostepid " & vbCrLf & _
                               "LEFT JOIN historymainline h ON h.historymainlineid=t.historymainlineid " & vbCrLf & _
                               "LEFT JOIN container c ON c.containerid=h.containerid " & vbCrLf & _
                               "LEFT JOIN Userattribute u ON u.parentid=h.containerid AND u.userattributename='printType' " & vbCrLf & _
                               "WHERE wt.workflowstepname='物料发料' "

        Dim DT As DataTable = dal.StandardGetDataTable(strSql)

        For Each row As DataRow In DT.Rows
            If row.IsNull("attributevalue") = True Then
                Dim conEntity As New ContainerStartInfo
                conEntity.ServerHostInfo = serverInfo
                conEntity.ContainerName = row("containername").ToString()
                conEntity.AttributeName = "printType"
                conEntity.attributeValue = "1"
                Dim result1 As uMESProcessResult = CommonObject.SetUserAttribute(conEntity)
            End If
        Next

    End Sub
#End Region

#End Region

#Region "更新派工单"
    Public Function UpdateWorkCenterDispatching(ByVal oExamle As DispatchingPara) As uMESProcessResult Implements uMESDispatchIBusiness.UpdateWorkCenterDispatching

        Dim strPlannedStartDate, strPlannedCompletionDate, strMainMfgEmployee, strResource, strWorkcenterdispatchingname, strMainMfgEmployeeid, strResourceid As String
        strPlannedStartDate = oExamle.PlannedStartDate
        strPlannedCompletionDate = oExamle.PlannedCompletionDate

        strMainMfgEmployee = oExamle.MainMfgEmployee
        strMainMfgEmployeeid = GetEmployeIDByName(strMainMfgEmployee)

        strResource = oExamle.Resource
        strResourceid = GetResourceIDByName(strResource)

        strWorkcenterdispatchingname = oExamle.Name

        Dim qty As Integer
        qty = oExamle.Qty

        Dim strSQL As String

        strSQL = " UPDATE workcenterdispatching SET qty = " & qty & ",resourceid = '" & strResourceid & "' ,plannedstartdate =  TO_DATE('" & strPlannedStartDate & "' ,'yyyy-MM-dd')," & vbCrLf
        strSQL += " plannedcompletiondate = TO_DATE('" & strPlannedCompletionDate & "' ,'yyyy-MM-dd'), mainmfgemployeeid =  '" & strMainMfgEmployeeid & "'" & vbCrLf
        strSQL += " WHERE workcenterdispatchingname = '" & strWorkcenterdispatchingname & "' "


        Dim r As Integer
        r = DatabaseHelper.ExecuteSql(strSQL)
        Dim result As New uMESProcessResult()
        If r = 0 Then
            result.Result = False
            result.Message = "派工单改派失败！"
            Return result

        Else
            result.Result = True
            result.Message = "派工单改派成功！"
            Return result
        End If
    End Function
#End Region

#Region "根据派工单ID删除派工单"
    Function DeleteWorkCenterDispatching(ByVal dtWCDList As DataTable) As uMESProcessResult Implements uMESDispatchIBusiness.DeleteWorkCenterDispatching

        ''派工单ID列表
        Dim strWCDList As String = ""
        For i As Integer = 0 To dtWCDList.Rows.Count - 1
            Dim strWCDID As String = dtWCDList.Rows(i)("DispatchingName").ToString
            If strWCDList = "" Then
                strWCDList = "'" & strWCDID & "'"
            Else
                strWCDList += ",'" & strWCDID & "'"
            End If
        Next

        Dim strDel As String = ""
        ''获取派工单所有的零件编号
        strDel = "DELETE FROM workcenterdispatching WHERE workcenterdispatchingname IN (" & strWCDList & ")"

        Dim r As Integer
        r = DatabaseHelper.ExecuteSql(strDel)
        Dim result As New uMESProcessResult()
        If r = 0 Then
            result.Result = False
            result.Message = "删除派工单失败！"
            Return result

        Else
            result.Result = True
            result.Message = "删除派工单成功！"
            Return result
        End If

    End Function
#End Region

#Region "获取用户信息"
    Function GetEmployeIDByName(ByVal strCheckName As String) As String

        GetEmployeIDByName = ""
        Dim strSQL As String

        strSQL = "SELECT e.employeeid" & vbCrLf &
                 "FROM employee e " & vbCrLf &
                 "WHERE e.employeename = '" & strCheckName & "'"
        Dim dt As New DataTable
        dt = DatabaseHelper.Query_ReturnDatTable(strSQL)

        If dt.Rows.Count > 0 Then
            GetEmployeIDByName = dt.Rows(0).Item("employeeid")

        End If
    End Function
#End Region

#Region "获取设备信息"
    Function GetResourceIDByName(ByVal strCheckName As String) As String

        GetResourceIDByName = ""
        Dim strSQL As String

        strSQL = "SELECT r.resourceid" & vbCrLf &
                 "FROM resourcedef r " & vbCrLf &
                 "WHERE r.resourcename = '" & strCheckName & "'"
        Dim dt As New DataTable
        dt = DatabaseHelper.Query_ReturnDatTable(strSQL)

        If dt.Rows.Count > 0 Then
            GetResourceIDByName = dt.Rows(0).Item("resourceid")

        End If
    End Function
#End Region

#Region "批量更新派工单"
    Public Function MultiUpdateWorkCenterDispatching(ByVal paraList As List(Of DispatchingPara)) As uMESProcessResult Implements uMESDispatchIBusiness.MultiUpdateWorkCenterDispatching
        Dim result As New uMESProcessResult()
        Dim strInfo As String = ""
        Dim qty As Integer
        Dim strPlannedStartDate, strPlannedCompletionDate, strMainMfgEmployee, strResource, strWorkcenterdispatchingname, strMainMfgEmployeeid, strResourceid As String
        Dim m_AttrList As New Dictionary(Of String, List(Of ClientAPIEntity))

        Try
            strPlannedStartDate = paraList(0).PlannedStartDate
            strPlannedCompletionDate = paraList(0).PlannedCompletionDate
            strMainMfgEmployee = paraList(0).MainMfgEmployee
            strMainMfgEmployeeid = GetEmployeIDByName(paraList(0).MainMfgEmployee)
            strResource = paraList(0).Resource
            strResourceid = GetResourceIDByName(paraList(0).Resource)
            qty = paraList(0).Qty
            strWorkcenterdispatchingname = ""

            For i As Integer = 0 To paraList.Count - 1
                strWorkcenterdispatchingname = strWorkcenterdispatchingname & paraList(i).Name & "','"
            Next

            If strWorkcenterdispatchingname.Length > 0 Then
                strWorkcenterdispatchingname = strWorkcenterdispatchingname.Substring(0, strWorkcenterdispatchingname.Length - 3)
            End If
          
            Dim strSQL As String
            strSQL = " UPDATE workcenterdispatching SET qty = " & qty & ",resourceid = '" & strResourceid & "' ,plannedstartdate =  TO_DATE('" & strPlannedStartDate & "' ,'yyyy-MM-dd')," & vbCrLf
            strSQL += " plannedcompletiondate = TO_DATE('" & strPlannedCompletionDate & "' ,'yyyy-MM-dd'), mainmfgemployeeid =  '" & strMainMfgEmployeeid & "'" & vbCrLf
            strSQL += " WHERE workcenterdispatchingname in ('" & strWorkcenterdispatchingname & "') "

            Dim r As Integer
            r = DatabaseHelper.ExecuteSql(strSQL)

            If r = 0 Then
                result.Result = False
                result.Message = "派工单改派失败！"
                Return result
            Else
                result.Result = True
                result.Message = "派工单改派成功！"
                Return result
            End If
        Catch ex As Exception
            result.Result = False
            result.Message = "派工单改派失败！"
            Return result
        End Try
    End Function
#End Region

#Region "查询工艺路线"
    Public Function GetWorkFlowByDispatching(ByVal paraContainerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkFlowByDispatching
        Dim result As New uMESProcessResult()
        Try
            Dim strSQL As String
            strSQL = " SELECT a.workflowid " & vbCrLf
            strSQL += " FROM WORKFLOWSTEP a JOIN currentstatus b ON a.workflowstepid = b.workflowstepid " & vbCrLf
            strSQL += " JOIN container c ON b.currentstatusid = c.currentstatusid " & vbCrLf
            strSQL += " WHERE c.containername = '" & paraContainerName & "'"

            Dim r As Integer
            r = DatabaseHelper.ExecuteSql(strSQL)
            Dim dt As New DataTable
            dt = DatabaseHelper.Query_ReturnDatTable(strSQL)

            If dt.Rows.Count > 0 Then
                result.Result = True
                result.DBTable = dt
                result.RowCount = dt.Rows.Count
                result.Message = ""
                Return result
            Else
                result.Result = False
                result.RowCount = 0
                result.Message = ""
                Return result
            End If
        Catch ex As Exception
            result.Result = False
            result.Message = ""
            Return result
        End Try
    End Function

#End Region

#Region "工时登记表"
    Function InsertLYworkingTimeInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.InsertLYworkingTimeInfo
        Dim employeeName As String = para("employeename").ToString.Trim()
        Dim containerName As String = para("containername").ToString.Trim()
        Dim specName As String = para("specname").ToString.Trim()
        Dim qty As Integer = Convert.ToInt32(para("qty").ToString.Trim())
        Dim dispSpecName As String = para("dispspecname").Trim()
        Dim rspecName As String = para("specname").Trim()
        Dim durationperunit As Decimal = 0
        Dim worktime As Decimal = 0
        Dim stepID As String = para("workStepID").Trim()
        Dim employID As String = para("employID")

        Dim result As New uMESProcessResult()
        '工时登记校验
        Dim strSql As String = String.Format("select * from LYWORKINGTIMEINFO " +
                                             "where CONTAINERNAME ='{0}' and employeeid='{1}' and SPECNAME='{2}' and QTY = '{3}' and " +
                                             "RSPECNAME='{4}' and STEPID='{5}'", containerName, employID, dispSpecName, qty, rspecName, stepID)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count > 0 Then
            result.Result = True
            Return result
            Exit Function
        End If
        Dim r1 As uMESProcessResult = GetStepDurationperunit(stepID, containerName)
        If r1.Result = True Then
            If r1.DBTable.Rows.Count > 0 Then
                Try
                    durationperunit = Convert.ToDecimal(r1.DBTable.Rows(0)("durationperunit"))
                Catch e As Exception
                    durationperunit = 0
                End Try
            Else
                durationperunit = 0
            End If
        End If
        worktime = durationperunit * qty

        strSql = "INSERT INTO LYWORKINGTIMEINFO (CONTAINERNAME,EMPLOYEENAME,SPECNAME,WORKTIME,QTY,UNITWORKTIME,RSPECNAME,STEPID,employeeid) " & vbCrLf
        strSql += "VALUES ('" & containerName.Trim() & "','" & employeeName.Trim() & "','" & dispSpecName.Trim() & "'," & worktime & "," & qty & "," & durationperunit & ",'" & rspecName & "','" & stepID & "','" & employID & "')"

            Dim r As Integer
            r = DatabaseHelper.ExecuteSql(strSql)
            If r = 0 Then
                result.Result = False
                result.Message = "工时登记失败！"
                Return result

            Else
                result.Result = True
                Return result
            End If
    End Function
#End Region

#Region "获取工序工时"
    Public Function GetSpecDurationperunit(ByVal specName As String, ByVal strContianerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetSpecDurationperunit

        Dim strSql As String = "SELECT c.containername,wt.workflowstepid,wt.workflowstepname,NVL(st.durationperunit,0) as durationperunit,sb.specname  FROM container c " & vbCrLf & _
                               "LEFT JOIN currentstatus cus ON cus.currentstatusid=c.currentstatusid " & vbCrLf & _
                               "LEFT JOIN Workflowstep wt ON wt.workflowstepid=cus.workflowstepid " & vbCrLf & _
                               "LEFT JOIN stepschedulingdetail st ON st.stepid=wt.workflowstepid " & vbCrLf & _
                               "LEFT JOIN spec s ON s.specid=cus.specid " & vbCrLf & _
                               "LEFT JOIN specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                               "WHERE sb.specname='" & specName & "' AND c.containername='" & strContianerName & "'"


        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.StandardGetDataTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Public Function GetStepDurationperunit(ByVal stepID As String, ByVal strContianerName As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetStepDurationperunit

        'Dim strSql As String = "SELECT c.containername,wt.workflowstepid,wt.workflowstepname,NVL(st.durationperunit,0) as durationperunit,sb.specname  FROM container c " & vbCrLf & _
        '                       "LEFT JOIN currentstatus cus ON cus.currentstatusid=c.currentstatusid " & vbCrLf & _
        '                       "LEFT JOIN Workflowstep wt ON wt.workflowstepid=cus.workflowstepid " & vbCrLf & _
        '                       "LEFT JOIN stepschedulingdetail st ON st.stepid=wt.workflowstepid " & vbCrLf & _
        '                       "LEFT JOIN spec s ON s.specid=cus.specid " & vbCrLf & _
        '                       "LEFT JOIN specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
        '                       "WHERE sb.specname='" & specName & "' AND c.containername='" & strContianerName & "'"

        Dim strSql As String = "SELECT c.containername,wt1.workflowstepid,wt1.workflowstepname,wt1.sequence,NVL(st.durationperunit,0) as durationperunit  FROM container c " & vbCrLf & _
                               "LEFT JOIN currentstatus cus ON cus.currentstatusid=c.currentstatusid " & vbCrLf & _
                               "LEFT JOIN Workflowstep wt ON wt.workflowstepid=cus.workflowstepid " & vbCrLf & _
                               "LEFT JOIN workflow w ON w.workflowid=wt.workflowid " & vbCrLf & _
                               "LEFT JOIN workflowstep wt1 ON wt1.workflowid=w.workflowid " & vbCrLf & _
                               "LEFT JOIN stepschedulingdetail st ON st.stepid=wt1.workflowstepid " & vbCrLf & _
                               "WHERE  c.containername='" & strContianerName & "' AND wt1.workflowstepid='" & stepID & "' ORDER BY wt1.sequence"

        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.StandardGetDataTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function


    Public Function GetSpecUnitTime(ByVal stepID As String) As uMESProcessResult Implements uMESDispatchIBusiness.GetSpecUnitTime
        Dim strSql As String = "SELECT nvl(durationperunit,0) as durationperunit FROM stepschedulingdetail t WHERE  t.stepid='" & stepID & "'"
        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.StandardGetDataTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

#End Region

#Region "获取工人报工工时"
    Function GetWorkingReportListNew(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkingReportListNew
        GetWorkingReportListNew = New uMESProcessResult

        Dim strSql As String = "SELECT p.productid,pb.productname,c.containername,e.employeename,e.fullname,t.examinetime,t.examinetimeemployeename,t.id, " & vbCrLf & _
                               "t.notes,t.reporttime,t.state,t.worktime,t.qty,t.unitworktime,t.rspecname,trim(t.specname) AS specname FROM LYWORKINGTIMEINFO t  " & vbCrLf & _
                               "LEFT JOIN container c ON c.containername=trim(t.containername)  " & vbCrLf & _
                               "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "LEFT JOIN employee e ON e.fullname=trim(t.employeename)  where (t.state=0 OR t.state=1) and  lower(e.employeename)='" & para("employeename").ToString.ToLower & "'"

        If para.ContainsKey("productname") Then
            strSql = strSql + " and pb.productname='" & para("productname") & "'"
        End If

        If para.ContainsKey("status") Then
            strSql = strSql + " and t.state=" & para("status")
        End If

        strSql = strSql + "  and t.reporttime between to_date('" & para("begindate") & "','yyyy-mm-dd hh24:mi:ss') and to_date('" & para("enddate") & "','yyyy-mm-dd hh24:mi:ss') "

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            r.DBTable.Columns.Add("stateName")
            For Each row As DataRow In r.DBTable.Rows
                If Convert.ToInt32(row("state")) = 0 Then
                    row("stateName") = "未提交"
                ElseIf Convert.ToInt32(row("state")) = 1 Then
                    row("stateName") = "已提交"
                End If
            Next

            Dim dataTmp As DataTable = dal.StandardGetDataTable(strSql)


            result.Result = True
            result.DBDataSet = New DataSet()
            result.DBDataSet.Tables.Add(dataTmp)
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        GetWorkingReportListNew = result
    End Function
#End Region

#Region "提交工时"
    Public Sub SubmmiWorkTime(ByVal dtworking As DataTable) Implements uMESDispatchIBusiness.SubmmiWorkTime

        Dim strDate As String = DateTime.Today.ToShortDateString()

        Dim strSQL As String
        For Each dr As DataRow In dtworking.Rows
            strSQL = String.Format("update LYWORKINGTIMEINFO set STATE=1, SUMITDATE=to_date('" & strDate & "','yyyy-MM-dd')  where ID='{0}'", dr("ID").ToString().Trim())
            dal.ExecuteSql(strSQL)
        Next
    End Sub

    Public Sub ChageWorkTime(ByVal dtworking As DataTable) Implements uMESDispatchIBusiness.ChageWorkTime

        Dim strDate As String = DateTime.Today.ToShortDateString()
        Dim strSQL As String
        For Each dr As DataRow In dtworking.Rows
            strSQL = String.Format("update LYWORKINGTIMEINFO set STATE={0}, EXAMINETIME=to_date('" & strDate & "','yyyy-MM-dd')   where ID='{1}'", dr("state").ToString().Trim(), dr("ID").ToString().Trim())
            dal.ExecuteSql(strSQL)
        Next
    End Sub

    Public Sub ChageWorkTimeHS(ByVal dtworking As DataTable) Implements uMESDispatchIBusiness.ChageWorkTimeHS

        Dim strDate As String = DateTime.Today.ToShortDateString()
        Dim strSQL As String
        For Each dr As DataRow In dtworking.Rows
            strSQL = String.Format("update LYWORKINGTIMEINFO set STATE={0},worktime={1},UNITWORKTIME={2}, EXAMINETIME=to_date('" & strDate & "','yyyy-MM-dd')  where ID='{3}'",
                                   dr("state").ToString().Trim(), dr("worktime").ToString().Trim(), dr("unitworktime").ToString().Trim(), dr("ID").ToString().Trim())
            dal.ExecuteSql(strSQL)
        Next
    End Sub
#End Region

#Region "查找工时信息"
    Function GetWorkingTime(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult Implements uMESDispatchIBusiness.GetWorkingTime

        Dim strSql As String = "SELECT w.workcentername,tea.teamname,f.factoryname,op.operationname,pb.productname,pf.productfamilyname,NVL(st.durationperunit,0) as durationperunit," & vbCrLf & _
                               "e.description,trim(t.employeename) AS employeename,TRIM(t.containername) AS containername,TRIM(t.specname) AS specname,t.id," & vbCrLf & _
                               "t.examinetime,t.examinetimeemployeename,t.notes,t.state,t.worktime,t.qty,t.unitworktime,t.REPORTTIME,t.RSPECNAME,t.SUMITDATE,(t.qty*t.unitworktime) as alltime,(t.qty* NVL( st.durationperunit,0)) as alltime1  FROM LYWORKINGTIMEINFO t " & vbCrLf & _
                               "LEFT JOIN employee e ON e.employeeid=t.employeeid " & vbCrLf & _
                               "LEFT JOIN sessionvalues se ON se.sessionvaluesid=e.sessionvaluesid " & vbCrLf & _
                               "LEFT JOIN teamemployee te ON te.employeeid = e.employeeid " & vbCrLf & _
                               "LEFT JOIN team tea ON tea.teamid=te.teamid " & vbCrLf & _
                               "LEFT JOIN workcenter w ON w.workcenterid=se.workcenterid " & vbCrLf & _
                               "LEFT JOIN factory f ON  f.factoryid=se.factoryid " & vbCrLf & _
                               "LEFT JOIN operation op ON op.operationid=se.operationid " & vbCrLf & _
                               "LEFT JOIN container c ON c.containername=TRIM(t.containername) " & vbCrLf & _
                               "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "LEFT JOIN stepschedulingdetail st ON st.stepid=t.STEPID " & vbCrLf & _
                               "LEFT JOIN productfamily pf ON pf.productfamilyid=p.productfamilyid  where 1=1 "

        If para("employeename").ToString() <> "全部" Then
            strSql = strSql + " and lower(trim(t.employeename))='" & para("employeename").ToString.ToLower() & "'"
        End If

        If para("factoryname").ToString() <> "全部" Then
            strSql = strSql + "  and f.factoryname='" & para("factoryname").ToString() & "'"
        End If

        If para("workcentername").ToString() <> "全部" Then
            strSql = strSql + " and w.workcentername='" & para("workcentername").ToString() & "'"
        End If

        If para("operationname").ToString() <> "全部" Then
            strSql = strSql + " and op.operationname='" & para("operationname").ToString() & "'"
        End If

        If para("productfamilyname").ToString() <> "全部" Then
            strSql = strSql + " and pf.productfamilyname='" & para("productfamilyname").ToString() & "'"
        End If

        If para("team").ToString() <> "全部" Then
            strSql = strSql + " and tea.teamname='" & para("team").ToString() & "'"
        End If

        If para.ContainsKey("specname") Then
            strSql = strSql + " and TRIM(t.specname) like '%" & para("specname").ToString() & "%'"
        End If

        If para.ContainsKey("productname") Then
            strSql = strSql + " and pb.productname='" & para("productname").ToString().Trim() & "'"
        End If

        If para.ContainsKey("containername") Then
            strSql = strSql + " and TRIM(t.containername) like '%" & para("containername").ToString().Trim() & "%'"
        End If

        If para("status").ToString().Trim() <> "5" Then
            strSql = strSql + "and t.state=" & para("status").ToString()
        End If

        If para.ContainsKey("begindate") Then
            strSql = strSql + String.Format(" and t.SUMITDATE >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para("begindate"))
        End If
        If para.ContainsKey("enddate") Then
            strSql = strSql + String.Format(" and t.SUMITDATE <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para("enddate"))
        End If
        If para.ContainsKey("reportBDate") Then
            strSql = strSql + String.Format(" and t.REPORTTIME >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para("reportBDate"))
        End If
        If para.ContainsKey("reportEDate") Then
            strSql = strSql + String.Format(" and t.REPORTTIME <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para("reportEDate"))
        End If
        strSql = strSql + " order by t.SUMITDATE desc, TRIM(t.specname),TRIM(t.containername)"

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As New uMESProcessResult

        Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
        r.DBTable.Columns.Add("stateName")


        For Each row As DataRow In r.DBTable.Rows
            If Convert.ToInt32(row("state")) = 0 Then
                row("stateName") = "未提交"
                'row("worktime") = Convert.ToInt32(row("durationperunit")) * Convert.ToInt32(row("qty"))
                row("worktime") = row("alltime1")
                row("unitworktime") = row("durationperunit")

            ElseIf Convert.ToInt32(row("state")) = 1 Then
                row("stateName") = "已提交"
                ' row("worktime") = Convert.ToInt32(row("durationperunit")) * Convert.ToInt32(row("qty"))
                row("worktime") = row("alltime1")
                row("unitworktime") = row("durationperunit")

            ElseIf Convert.ToInt32(row("state")) = 3 Then
                row("stateName") = "已核算"
            ElseIf Convert.ToInt32(row("state")) = 4 Then
                row("stateName") = "已核销"
            End If
        Next

        Dim tmpTable As DataTable = dal.StandardGetDataTable(strSql)

        Dim strTmp As String = para("workTimeDate").ToString().Trim()

        For Each row As DataRow In tmpTable.Rows
            If Convert.ToInt32(row("state")) = 0 Or Convert.ToInt32(row("state")) = 1 Then
                row("worktime") = row("alltime1")
                row("unitworktime") = row("durationperunit")
            End If
            If strTmp.Trim() <> "" Then
                Dim dateTmp As DateTime = Convert.ToDateTime(strTmp)
                If Convert.ToDateTime(row("REPORTTIME")) <= dateTmp Then
                    row("worktime") = 0
                End If
            End If
        Next

        result.DBDataSet = New DataSet()
        result.DBDataSet.Tables.Add(tmpTable)
        result.Result = True
        result.DBTable = r.DBTable
        result.RowCount = r.RowCount
        Return result

    End Function

#End Region

#Region "报工检验操作者补全"
    Public Sub UpdateReporterForWorkingReportAndCheck() Implements uMESDispatchIBusiness.UpdateReporterForWorkingReportAndCheck
        Dim strSQL As String
        '更新workingreporthistory
        strSQL = "select h.workingreporthistoryid,hml.employeeid from workingreporthistory h left join historymainline hml on hml.historymainlineid=h.historymainlineid where h.reportemployeeid is null"
        Dim tmpTable As DataTable = dal.StandardGetDataTable(strSQL)
        For Each dr As DataRow In tmpTable.Rows
            strSQL = String.Format("update workingreporthistory t set t.reportemployeeid='{0}' where t.workingreporthistoryid ='{1}'",
                                   dr("employeeid"), dr("workingreporthistoryid"))
            dal.ExecuteSql(strSQL)
        Next

        '更新speclastcheckhistory
        strSQL = "select h.speclastcheckhistoryid,hml.employeeid from speclastcheckhistory h left join historymainline hml on hml.historymainlineid=h.historymainlineid where h.reportemployeeid is null"
        tmpTable = dal.StandardGetDataTable(strSQL)
        For Each dr As DataRow In tmpTable.Rows
            strSQL = String.Format("update speclastcheckhistory t set t.reportemployeeid='{0}' where t.speclastcheckhistoryid ='{1}'",
                                   dr("employeeid"), dr("speclastcheckhistoryid"))
            dal.ExecuteSql(strSQL)
        Next

        '更新routinecheckhistory
        strSQL = "select h.routinecheckhistoryid,hml.employeeid from routinecheckhistory h left join historymainline hml on hml.historymainlineid=h.historymainlineid where h.reportemployeeid is null"
        tmpTable = dal.StandardGetDataTable(strSQL)
        For Each dr As DataRow In tmpTable.Rows
            strSQL = String.Format("update routinecheckhistory t set t.reportemployeeid='{0}' where t.routinecheckhistoryid ='{1}'",
                                   dr("employeeid"), dr("routinecheckhistoryid"))
            dal.ExecuteSql(strSQL)
        Next

        '更新movehistory
        strSQL = "select h.movehistoryid,hml.employeeid from movehistory h left join historymainline hml on hml.historymainlineid=h.historymainlineid where h.reportemployeeid is null"
        tmpTable = dal.StandardGetDataTable(strSQL)
        For Each dr As DataRow In tmpTable.Rows
            strSQL = String.Format("update movehistory t set t.reportemployeeid='{0}' where t.movehistoryid ='{1}'",
                                   dr("employeeid"), dr("movehistoryid"))
            dal.ExecuteSql(strSQL)
        Next
    End Sub
#End Region

#Region "查找报工记录"
    Public Function GetSysReport(ByVal strDateTmp As String) As DataTable Implements uMESDispatchIBusiness.GetSysReport
        Dim sb As New StringBuilder()
        Dim strSql As String = "select f.factoryname,w.workcentername,t.teamname,o.operationname,pf.productfamilyname,c.containername,wrh.qty,hm.txndate,em.employeeid, " & vbCrLf & _
                               "em.employeename,em.fullname,wrh.workingreporthistoryid,wrh.SUBMMITDATE, wfs.workflowstepname, s.specid, " & vbCrLf & _
                               "nvl(std.durationperunit,spd.durationperunit*24*60) as durationperunit, c.productid, p.productbaseid,pb.productname," & vbCrLf & _
                               "(wrh.qty)* nvl(std.durationperunit,spd.durationperunit*24*60) as sumworktime ,wfs.workflowstepid,sb.specname,sb1.specname AS specname1 " & vbCrLf & _
                               "from workingreporthistory wrh " & vbCrLf & _
                               "inner join conventioncheckhistory cc on cc.workingreportid=wrh.workingreporthistoryid " & vbCrLf & _
                               "left join historymainline hm on hm.historymainlineid = wrh.historymainlineid " & vbCrLf & _
                               "left join employee em on em.employeeid = hm.employeeid " & vbCrLf & _
                               "left join sessionvalues se on se.employeeid = em.employeeid " & vbCrLf & _
                               "left join teamemployee te on te.employeeid = em.employeeid " & vbCrLf & _
                               "left join workflowstep wfs on wfs.workflowstepid=hm.workflowstepid " & vbCrLf & _
                               "left join spec s on s.specid=wfs.specid " & vbCrLf & _
                               "LEFT JOIN specbase sb1 ON sb1.specbaseid=s.specbaseid " & vbCrLf & _
                               "left join specbase sb on sb.specbaseid=wfs.specbaseid " & vbCrLf & _
                               "left join specschedulingdetail spd on spd.specid=nvl(s.specid,sb.revofrcdid) " & vbCrLf & _
                               "left join stepschedulingdetail std on std.stepschedulingdetailid=wfs.schedulingdetailid " & vbCrLf & _
                               "left join container c on c.containerid=hm.containerid " & vbCrLf & _
                               "left join product p on p.productid=c.productid " & vbCrLf & _
                               "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "left join team t on t.teamid=te.teamid " & vbCrLf & _
                               "left join operation o on o.operationid = se.operationid " & vbCrLf & _
                               "left join sessionvalues sv on sv.employeeid=em.employeeid " & vbCrLf & _
                               "left join factory f on f.factoryid=sv.factoryid " & vbCrLf & _
                               "left join workcenter w on w.workcenterid=sv.workcenterid " & vbCrLf & _
                               "left join productfamily pf on pf.productfamilyid=p.productfamilyid where hm.reversalstatus = 1 And c.status <> 0  order by hm.txndate desc"

        Dim tmpTable As DataTable = dal.StandardGetDataTable(strSql)


        If strDateTmp.Trim() <> "" Then
            Dim dateTmp As DateTime = Convert.ToDateTime(strDateTmp)
            For Each row As DataRow In tmpTable.Rows
                If Convert.ToDateTime(row("txndate")) <= dateTmp Then
                    row("sumworktime") = 0
                End If
            Next
        End If

        For Each row As DataRow In tmpTable.Rows
            If row.IsNull("specname") = True Then
                row("specname") = row("specname1")
            End If
        Next

        Return tmpTable
    End Function
#End Region

    Public Sub InsertLYworkingTimeInfoTemp(ByVal employeeName As String, ByVal containerName As String, ByVal specName As String,
                                           ByVal qty As Integer, ByVal dispSpecName As String, ByVal intUnitTime As Integer,
                                           ByVal intState As Integer, ByVal stepID As String, ByVal reportDate As Date, ByVal submitDate As String, ByVal allWorkTime As Integer) Implements uMESDispatchIBusiness.InsertLYworkingTimeInfoTemp

        Dim worktime As Integer = 0

        Dim strTmpDate = reportDate.ToShortDateString()
        Dim strSql As String = ""

        If submitDate.Trim() = "" Then
            strSql = "INSERT INTO LYWORKINGTIMEINFO (CONTAINERNAME,EMPLOYEENAME,SPECNAME,WORKTIME,QTY,UNITWORKTIME,RSPECNAME,STATE,STEPID,reporttime) " & vbCrLf
            strSql += "VALUES ('" & containerName.Trim() & "','" & employeeName.Trim() & "','" & dispSpecName.Trim() & "'," & allWorkTime & "," & qty & "," & intUnitTime & ",'" & specName & "'," & intState & ",'" & stepID & "',to_date('" & strTmpDate & "','yyyy-MM-dd'))"
        Else
            strSql = "INSERT INTO LYWORKINGTIMEINFO (CONTAINERNAME,EMPLOYEENAME,SPECNAME,WORKTIME,QTY,UNITWORKTIME,RSPECNAME,STATE,STEPID,reporttime,SUMITDATE) " & vbCrLf
            strSql += "VALUES ('" & containerName.Trim() & "','" & employeeName.Trim() & "','" & dispSpecName.Trim() & "'," & allWorkTime & "," & qty & "," & intUnitTime & ",'" & specName & "'," & intState & ",'" & stepID & "',to_date('" & strTmpDate & "','yyyy-MM-dd'),to_date('" & submitDate & "','yyyy-MM-dd')  )"
        End If

        Dim r As Integer
        r = DatabaseHelper.ExecuteSql(strSql)
    End Sub

    '获取批次需生产准备的工序
    Public Function GetMultiSpecDispatchPrepareInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESDispatchIBusiness.GetMultiSpecDispatchPrepareInfo
        GetMultiSpecDispatchPrepareInfo = New uMESProcessResult
        Dim sb As New StringBuilder()

        sb.AppendLine("select c.containername,wcd.dispatchdate,sb.specname,wcd.workcenterdispatchingid ")
        sb.AppendLine("from workcenterdispatching wcd ")
        sb.AppendLine("left join container c on c.containerid= wcd.containerid")
        sb.AppendLine("left join spec s on s.specid=wcd.specid")
        sb.AppendLine("left join specbase sb on sb.specbaseid=s.specbaseid")
        sb.AppendFormat(" where  wcd.isprepare<>1 and c.containerid in ({0})  ",
                        para("ContainerList"))
        sb.AppendLine(" order by  c.containername,sb.specname")
        GetMultiSpecDispatchPrepareInfo.DBTable = dal.StandardGetDataTable(sb.ToString)
        GetMultiSpecDispatchPrepareInfo.Result = True
    End Function
    '更新派工记录准备状态
    Public Sub UpdateSpecDispatchPrepared(ByVal para As Dictionary(Of String, String)) Implements uMESDispatchIBusiness.UpdateSpecDispatchPrepared
        Dim strSQL As String = " UPDATE workcenterdispatching SET isprepare =1,"
        strSQL += String.Format(" preparedate = to_date('{0}','yyyy-mm-dd hh24:mi:ss'), prepareemployeeid = '{1}'", para("preparedate"), para("prepareemployeeid"))
        strSQL += String.Format(" WHERE workcenterdispatchingid = '{0}'", para("workcenterdispatchingid"))
        DatabaseHelper.ExecuteSql(strSQL)
    End Sub

    '更新工时记录报工人ID
    Public Sub UpdateLYWORKINGTIMEINFOEmployeeID() Implements uMESDispatchIBusiness.UpdateLYWORKINGTIMEINFOEmployeeID

        Dim strSQL As String = " select * from lyworkingtimeinfo "
        Dim worktimedt As DataTable = dal.StandardGetDataTable(strSQL)
        strSQL = " select c.containername,hml.workflowstepid,w.reportemployeeid from workingreporthistory w " +
                "left join historymainline hml on hml.historymainlineid=w.historymainlineid " +
                "left join container c on c.containerid=hml.containerid "
        Dim workingreportdt As DataTable = dal.StandardGetDataTable(strSQL)
        For Each dr As DataRow In worktimedt.Rows
            Dim drs = workingreportdt.Select(String.Format("containername='{0}' and workflowstepid='{1}'",
                                                           dr("containername").ToString.Trim(), dr("stepid").ToString.Trim()))
            If drs.Length > 0 Then
                strSQL = String.Format(" update lyworkingtimeinfo set employeeid='{0}' WHERE id = '{1}'",
                                       drs(0)("reportemployeeid"), dr("id"))
                DatabaseHelper.ExecuteSql(strSQL)
            End If

        Next

    End Sub
End Class
